JAVA高级面试进阶训练营视频教程

admin

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