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拼接,也是可以接受的吧。

SQL Where in list 問題

相關文章