SQL Where in list 問題

壹頁書發表於2014-12-21
SQL
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/
  1. create table nums(id int not null primary key);

  3. delimiter $$
  4. create procedure pCreateNums(cnt int)
  5. begin
  6.     declare s int default 1;
  7.     truncate table nums;
  8.     while s<=cnt do
  9.         insert into nums select s;
  10.         set s=s+1;
  11.     end while;
  12. end $$
  13. 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拼接,也是可以接受的吧。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1375383/，如需轉載，請註明出處，否則將追究法律責任。

相關文章