SQL Where in list 问题-CSDN博客

ITPUB技术丛书剑破冰山 170页

Where in list问题还是挺常见的。
前台可能传过来一系列参数,JAVA可能将其拼接在In列表里面.
比较典型的场景是前台页面有一堆复选框,系统需要显示用户勾选的数据
这时,后台很可能将其拼接为一个包含in列表的SQL
不过,这种做法有两个缺陷
1.Oracle In列表的数目有限制(1000)
2.不能复用执行计划,每次几乎都是硬解析.
3.In拼接可能存在SQL注入的风险

创建一个实验表
create table t as select rownum id,'name'||rownum name from dual connect by level<5000;

部分结果如图

加入前台将Id传递过来,JAVA将其拼接为In列表
假设前台传来(10,20,25),先将其转为一个表
var str varchar2(200);
exec :str:='10,20,25';

SELECT REGEXP_SUBSTR(:str, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= LENGTH(TRIM(TRANSLATE(:str, TRANSLATE(:str, ',', ' '), ' '))) + 1;

然后使用in就可以取得正确的结果
SELECT *
FROM t
WHERE id IN (SELECT REGEXP_SUBSTR(:str, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= LENGTH(TRIM(TRANSLATE(:str, TRANSLATE(:str, ',', ' '), ' '))) + 1);

或者采用如下方式也可以.
SELECT *
FROM t
WHERE id IN (SELECT REGEXP_SUBSTR(:str, '[^,]+', 1, LEVEL) AS value_str
   FROM DUAL
   CONNECT BY LEVEL <= length(:str)-length(replace(:str,',',''))+1);

如果没有正则表达式的支持,也可以使用如下方式
SELECT SUBSTR(inlist, INSTR(inlist, ',', 1, LEVEL) + 1, INSTR(inlist, ',', 1, LEVEL + 1) - INSTR(inlist, ',', 1, LEVEL) - 1) AS value_str
FROM (
   SELECT ',' || :str || ',' AS inlist
   FROM DUAL
)
CONNECT BY LEVEL <= LENGTH(:str) - LENGTH(REPLACE(:str, ',', NULL)) + 1

------------------------------------------------------------------------------------------------------
MySQL 处理这个问题需要增加数字辅助表
http://blog.itpub.net/29254281/viewspace-1362897/

create table nums(id int not null primary key);
delimiter $$
create procedure pCreateNums(cnt int)
begin
declare s int default 1;
truncate table nums;
while s<=cnt do
insert into nums select s;
set s=s+1;
end while;
end $$
delimiter ;

call pCreateNums(100000);

创建实验表
create table t
select id,concat('name',id) name from nums where id<5000;

将逗号分隔值转成临时表
set @str='10,20,25';

select substring_index(substring_index(@str,',',b.id),',',-1) value_str
from
nums b
where b.id <= (length(@str) - length(replace(@str,',',''))+1);

然后就明了了
select * from t where t.id in(
select substring_index(substring_index(a.str,',',b.id),',',-1) value_str
from
nums b,(select @str str from dual) a
where b.id <= (length(a.str) - length(replace(a.str,',',''))+1)
);

因为MySQL参数化仅仅是替换了值(http://blog.itpub.net/29254281/viewspace-1151799/)
所以SQL的长度和In其实没有什么区别,但是使用子查询而非常量的方式,可以应用MySQL内连接的改写,在inList很大的情况下，可以提升效率.
虽然MySQL没有像Oracle一样有In的数量限制,但是他对SQL的总长度有限制,使用 max_allowed_packet参数控制.

另外不要使用下面的写法处理 MySQL In List问题，因为他不能应用索引,效率很低
select * from t where find_in_set(id,@str);
或者
select * from t inner join
(select @str str from dual) a
on(instr(concat(',',a.str,','),concat(',',t.id,','))!=0);

MySQL where in List 问题和Oracle不同的是,
1.MySQL没有in列表数目的限制,只有SQL总长度的限制
2.MySQL都是硬解析

所以唯一的收获就是避免SQL注入攻击.
如果没有这个考虑,直接用in拼接,也是可以接受的吧。