创建测试表:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`str` varchar(45) DEFAULT NULL,
`thekey` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `thekey_UNIQUE` (`thekey`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
添加数据
insert into test (str,thekey) values(‘a’,’akey’),(‘b’,’bkey’),(‘c’,’ckey’),(‘d’,’dkey’),(‘e’,’ekey’);
创建存储过程:
DELIMITER $$
create procedure get_tests(in_ids varchar(200),thekey varchar(50))
begin
declare v_sql varchar(500);
set v_sql = concat(‘select * from test where id in (‘ ,in_ids , ‘) and thekey=?’ );#?问号,西药参数
set @sqlstr = v_sql;
prepare sqlcommand from @sqlstr;
set @thekey = thekey;#参数
execute sqlcommand using @thekey;
deallocate prepare sqlcommand;
end$$
DELIMITER ;
执行存储过程
call get_tests(‘1,2,3,4,5,10,14′,’key-2’);