Mysql中使用FIND_IN_SET解決IN條件為字串時只有第一個資料可用的問題

標籤：style   blog   http   color   使用   io   資料   for   

今天在使用Mysql的預存程序處理資料的大量刪除時，遇到了WHERE條件中使用IN(strlist)時（strlist為逗號分隔的字串），只有strlist的第一個元素才有效問題，現在將問題和解決方案做下記錄。

 

我們首先建立兩張表userinfo(使用者資訊表)和userextinfo(使用者擴充資訊表)，其中userextinfo表的UserID欄位為外鍵對應userinfo表中的UserID欄位

使用者資訊表userinfo

初始資料

 

使用者擴充資訊表userextinfo

初始資料

現在表中有6個使用者的資訊，倘若我們現在需要根據使用者ID刪除使用者“老二”、“老三”和“老四”的資料，則需要先刪除userextinfo表中資料然後再刪除userinfo表中的資料。若在代碼中分別對三個使用者執行sql語句，先後刪除userextinfo和userinfo的資訊，可以完成我們的目的。但是，當我們一次需要刪除的使用者越多時，在資料庫連接上的開銷就會增多。所以，我們現在可以採用預存程序，用一次資料庫連接來完成這個操作。

 

錯誤的方式：

我們建立預存程序P_User_Del

代碼如下：

 1 TOP: BEGIN 2 DECLARE EXIT HANDLER FOR SQLEXCEPTION 3 BEGIN 4     ROLLBACK;         5 END; 6      7 START TRANSACTION; 8      9     IF LENGTH(ExtUserIds)>0 THEN10       DELETE FROM userextinfo WHERE UserID IN(ExtUserIds); -- 刪除使用者擴充資訊11       DELETE FROM userinfo WHERE UserID IN(ExtUserIds); -- 刪除使用者資訊12     END IF;            13 14 COMMIT;    15 16 END

執行預存程序，輸入參數“2,3,4”

預存程序執行成功

此時，我們看到表中資料只刪除了“老二”的資料

這個問題是由於我們傳入的參數“ExtUserIds”是一個字串，sql語句中的IN只能使用字串中第一個逗號前的資料，所以在上面的例子中，只刪除了2號使用者的資料。

 

正確的方式：

當然，我們可以將字串“ExtUserIds”按逗號分開，然後迴圈刪除資訊，但是這麼做會增加不少的邏輯處理。

現在我們不使用“IN”而使用“FIND_IN_SET”，將預存程序修改為：

代碼如下：

 1 TOP: BEGIN 2 DECLARE EXIT HANDLER FOR SQLEXCEPTION 3 BEGIN 4     ROLLBACK;         5 END; 6      7 START TRANSACTION; 8      9     IF LENGTH(ExtUserIds)>0 THEN10       DELETE FROM userextinfo WHERE FIND_IN_SET(UserID,ExtUserIds); -- 刪除使用者擴充資訊11       DELETE FROM userinfo WHERE FIND_IN_SET(UserID,ExtUserIds); -- 刪除使用者資訊12     END IF;            13 14 COMMIT;    15 16 END

再次執行預存程序，結果如下：

我們想要刪除的3個使用者資訊全部清除掉了。

至於方法“FIND_IN_SET(str,strlist)”的其他應用情境大家可以自己查閱。