An issue in MySQL where only the first data is available when you use Find_in_set to resolve an in condition as a string

Today, when using MySQL's stored procedures to process bulk deletions of data, when using in (Strlist) in the Where condition (Strlist is a comma-delimited string), only the first element of Strlist is valid, and now the problem and resolution are recorded.

We first create two tables userinfo (User Information table) and userextinfo (User extension Information table), where the UserID field of the Userextinfo table corresponds to the UserID fields in the UserInfo table

User Information Sheet UserInfo

Initial data

User Extensions Information Sheet userextinfo

Initial data

Now that the table has 6 user information, if we now need to delete the user "second", "Old Three" and "old four" data according to the user ID, you need to delete the data in the Userextinfo table before deleting the data in the UserInfo table. If you execute SQL statements on three users in your code and delete Userextinfo and userinfo, you can accomplish our goal. However, the more users we need to delete at a time, the more overhead it will have on the database connection. Therefore, we can now use a stored procedure to complete this operation with a database connection.

The wrong way:

We create a stored procedure P_user_del

The code is as follows:

1 TOP:BEGIN2 DECLARE EXITHANDLER forSQLEXCEPTION3 BEGIN4     ROLLBACK; 5 END;6     7STARTTRANSACTION;8     9     IFLENGTH (Extuserids)>0  ThenTen       DELETE  fromUserextinfoWHEREUseridinch(Extuserids);--Delete User extension information One       DELETE  fromUserInfoWHEREUseridinch(Extuserids);--Delete User Information A     END IF;  -  - COMMIT;  the  - END

Execute stored procedure, input parameter "2,3,4"

Successful execution of the stored procedure

At this point, we see that the data in the table only deletes the "Dick" data.

The problem is that the parameter "Extuserids" that we passed in is a string, in which the in SQL statement only uses the data before the first comma in the string, so in the above example, only the data of the number 2nd user is deleted.

The right way:

Of course, we can separate the string "Extuserids" by commas and then iterate over the information, but doing so will add a lot of logic to the process.

Now we are not using "in" and using "Find_in_set" to modify the stored procedure to:

The code is as follows:

1 TOP:BEGIN2 DECLARE EXITHANDLER forSQLEXCEPTION3 BEGIN4     ROLLBACK; 5 END;6     7STARTTRANSACTION;8     9     IFLENGTH (Extuserids)>0  ThenTen       DELETE  fromUserextinfoWHEREFind_in_set (Userid,extuserids);--Delete User extension information One       DELETE  fromUserInfoWHEREFind_in_set (Userid,extuserids);--Delete User Information A     END IF;  -  - COMMIT;  the  - END

Execute the stored procedure again with the following results:

All 3 User information we want to delete is erased.

For other application scenarios of the method "Find_in_set (str,strlist)" You can check it out for yourself.