출처1 <a href="https://mariadb.com/kb/en/library/find_in_set/" target="_blank" class="tx-link">https://mariadb.com/kb/en/library/find_in_set/</a>:&nbsp;출처2 :&nbsp;<a href="https://stackoverflow.com/questions/13809101/mysql-dynamic-in-clause-from-a-split-on-a-string" target="_blank" class="tx-link">https://stackoverflow.com/questions/13809101/mysql-dynamic-in-clause-from-a-split-on-a-string</a> <a href="http://motolies.tistory.com/697" target="_blank">2018/07/24 - [프로그램 자료/MySQL &amp; MariaDB] - [MySQL &amp; MariaDB] 동적 프로시저 dynamic procedure</a> [개발환경]MariaDB 10.0 개발작업을 하다보면 특히 조회동작시에 동적쿼리를 생각보다 많이 사용하게 된다.&nbsp; 개중엔 웹화면에서 checkbox로 작업하여 선택한 것들만 조회되도록 하는 경우도 있는데,&nbsp;mysql/mariadb 에서는 find_in_set 함수를 사용하여 구현할 수 있다. 우선 find_in_set 함수가 무슨 일을 하는지 알아야 할 것 같다.아래와 같은 쿼리를 실행하면 #(주석) 이후에 있는 출력값을 주어진다. <div class="txc-textbox" style="border-style: solid; border-width: 1px; border-color: rgb(254, 254, 184); background-color: rgb(254, 254, 184); padding: 10px;">set @clause = 'A,B,C';

select find_in_set('A', @clause); # 1

select find_in_set('B', @clause); # 2

select find_in_set('C', @clause); # 3

select find_in_set('D', @clause); # 0</div> 'A,B,C' 라는 문자열을 comma(,)로 자른 배열을 생성하고 거기에서 equal(=)로 검색하여 index를 반환한다. 이걸 사용해서 in clause 동작을 하도록 할 수 있다.주의할 점은 콤마 사이에 공백이 있다면 해당 칼럼에서 조회된 값에도 공백이 있어야 검색이 된다는 점이다.<div class="txc-textbox" style="border-style: solid; border-width: 1px; border-color: rgb(254, 254, 184); background-color: rgb(254, 254, 184); padding: 10px;">set @clause = 'A,B,C';

select t.*

from table as t

where find_in_set(t.tpye,
@clause ) &gt;
0;</div> 프로시저에서는 다음과 같이 사용가능하다. <div class="txc-textbox" style="border-style: solid; border-width: 1px; border-color: rgb(254, 254, 184); background-color: rgb(254, 254, 184); padding: 10px;">DELIMITER $$&nbsp;&nbsp;&nbsp; USE `dbname`$$&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; DROP PROCEDURE
IF EXISTS
usp_test$$&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; CREATE DEFINER=`userid`@`%` PROCEDURE
`usp_test`(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN `logTypeCode` varchar(20)&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; LANGUAGE SQL&nbsp;&nbsp;&nbsp; NOT DETERMINISTIC&nbsp;&nbsp;&nbsp; CONTAINS SQL&nbsp;&nbsp;&nbsp; SQL SECURITY DEFINER&nbsp;&nbsp;&nbsp; COMMENT '로그 조회 테스트'&nbsp;&nbsp;&nbsp; BEGIN&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (logTypeCode
is null
OR logTypeCode = '') then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SET @logTypeCode :=
'A,B,C';&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SET @logTypeCode :=
logTypeCode;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end if;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SET @Sql = "";&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SET @Sql = CONCAT(@Sql, CHAR(10),"select l.*");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SET @Sql = CONCAT(@Sql, CHAR(10),"from log_table
as l");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SET @Sql = CONCAT(@Sql, CHAR(10),"where 1=1 ");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SET @Sql = CONCAT(@Sql, CHAR(10)," and (find_in_set(l.logtype, ? ) &gt; 0)");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PREPARE dquery FROM
@Sql;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXECUTE dquery USING
@logTypeCode;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END$$DELIMITER ;































































&nbsp;</div>

[MySQL & MariaDB] 동적쿼리 사용시 in clause 사용하기