首页 > 代码库 > Mysql中使用FIND_IN_SET解决IN条件为字符串时只有第一个数据可用的问题

Mysql中使用FIND_IN_SET解决IN条件为字符串时只有第一个数据可用的问题

今天在使用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)”的其他应用场景大家可以自己查阅。