MYSQLのストアドプロシージャでIN区の中を可変にしたい

Question

前提

MySQLでストアドプロシージャを書いています。
「種別ID」をカラムに含むテーブルから、特定の種別IDに一致するレコード群をSelectするプロシージャです。
今現在のプロシージャは種別IDを1つだけ引数としてもらい、当該種別IDに一致するレコードをSelectします。
（引数がnullの場合は、全レコードをselectします。）

実現したいこと

今現在は種別ID1つのみを引数としてもらうプロシージャとなっていますが、
複数個の種別IDに一致するレコード群をSelectできるように改造したいです。

●例
・今
引数として種別ID「2」をもらう⇒「種別ID＝2」であるレコードがSELECTされる
・改造後
引数として種別ID「2,3,4」をもらう⇒「種別ID＝2 or 3 or 4」であるレコードがSELECTされる

該当のソースコード

今現在のプロシージャ

DELIMITER $$
CREATE PROCEDURE `hoge_procedure`(
    IN param_hoge_type_id INT #検索対象種別ID
    )
#以降にストアドプロシージャの処理を記述する
BEGIN
#出力対象の列を指定する
	SELECT
		hoge_id #ID抽出
		
	#データ取得元のテーブルを指定する
	FROM
	    hoge_table
	    
	WHERE
		hoge_type_id = IFNULL(param_hoge_type_id, hoge_type_id) #nullがきたら全部を検索するようにする
	; 
	
#ストアドプロシージャの処理を終える
END $$
delimiter ;

自分なりの方針

以下の方針でやり方がないか、探しています。
①引数の型を文字列型に変える（引数として「2,3,4」という文字列が与えられる想定）
②Whereの中に条件としてIN()を使い、IN()の中身を引数に応じて設定する

やりたいが都合上できないこと

hoge_tableのhoge_type_id カラムに含まれる値を1,2,3,4,5…の整数ではなく1,2,4,8,16…(2のべき乗)とし、Where条件としてビット演算をすればかんたんに実現できそうですが、カラム自体に格納されている値を変化させるのはシステム他部位への影響が甚大であるため、出来れば行いたくないです。

補足情報（FW/ツールのバージョンなど）

MySQL 5.7.33

Answer 1

解決してますけど文字列使うのがありならパラメータの区切り文字を前後につけて
種別IDの前後に区切り文字を加えてパラメータで部分一致すればいいです。
IDのような完全一致の複数検索の場合はこの手法でINを使わなくても対応できます。

例えば
引数として種別ID「2,3,4」をもらう⇒パラメータは「,2,3,4,」にする
データの種別IDは「1」の場合⇒「,1,」でパラメータ「,2,3,4,」は部分一致しない
データの種別IDは「2」の場合⇒「,2,」でパラメータ「,2,3,4,」は部分一致する
データの種別IDは「3」の場合⇒「,3,」でパラメータ「,2,3,4,」は部分一致する
データの種別IDは「4」の場合⇒「,4,」でパラメータ「,2,3,4,」は部分一致する
データの種別IDは「5」の場合⇒「,5,」でパラメータ「,2,3,4,」は部分一致しない

	SELECT
		hoge_id #ID抽出
		
	#データ取得元のテーブルを指定する
	FROM
	    hoge_table
	    
	WHERE
		','+IFNULL(param_hoge_type_id,cast(hoge_type_id as varchar(50)))+','
           like '%,'+cast(hoge_type_id as varchar(50))+',%'

mysql知らないんで適当に書いてます。param_hoge_type_idはコンマ区切りの文字列想定

Comments

[eleele28]

ありがとうございます！！こんな方法もあるんですね。

Answer 2

testデータ作成

create table tbl (id int primary key,val int);
insert into tbl values(1,1),(2,2),(3,5),(4,4),(5,2);
select * from tbl where val in(2,3,4);

procedue作成

drop procedure if exists proc;
delimiter //
create procedure proc(IN args varchar(50))
begin 
set @sql='select * from tbl where 1 ';
if args regexp "^(\\d+,)*\\d+$" then
set @sql:=concat(@sql,'and val in (',args,')');
else
set @sql:=concat(@sql,'and 0');
end if;
prepare stmt from @sql;
execute stmt;
end;
//
delimiter ;

※調整：数字の羅列以外はNGとする

procedure実行

call proc('2,3,4'); /*出力OK*/
call proc('2'); /*出力OK*/
call proc('6'); /*ヒットするデータはないがOK*/
call proc('x'); /*数値じゃないので戻り値がない*/

Comments

[eleele28]

ありがとうございます！！

[yambejp]

よく考えたらfind_in_setだけで解決するかもしれません

select * from tbl where find_in_set(val,’2,3,4');

[eleele28]

まさに今回のためにあるような関数ですね、ありがとうございます！！