Pay attention when using MySQL User-Defined Variables

 

Recently, i used a user-defined variable to store some values, and it showed an unexpected result. I updated rows using ‘where a in (@var)’, but this query just updated the first value of ‘@var’, instead updated all matched values. This article introduces how to store multiple values in a user-defined variables correctly, when using such variables, make sure the number of Rows matched, Changed, affected is right.

MySQL Version: 8.0.12

Problem Statement

 

First step, I store the result values in a variable:

 

Secondly, update the table rows:

In this step, after the update query is finished, the result shows, “1 row affected“, but there are 2 members in ‘@tmpvar’. Then i search the User-Defined Variables part of MySQL Server Reference Manual(https://dev.mysql.com/doc/refman/8.0/en/user-variables.html).

“If the value of a user variable is selected in a result set, it is returned to the client as a string.”

So when i store two values(123,1234) in ‘@tmpvar’, ‘@tmpvar’ returns me a string ‘123,1234’ actually, not ‘123’ and ‘1234’, and the update result is correct in this case. Also we can have a test.

 

Troubleshooting

I convert the ‘123,1234’ as a character string, then we get “Rows matched: 1” for these two update queries. So this is not a bug of MySQL, it’s just when using a user-defined variable to store multiple values, it is returned to the client as a string.

 

How to solve?

Then how can we use the user-defined variable to store multiple values and return expected result? I found two ways.

1. Splice a complete statement

In this way, i splice the ‘@tmpvar’ and other part of queries into a complete statement named ‘@sql’, and then execute this statement.

 

2. Use the FIND_IN_SET function

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

The second way is easier, just replacing the ‘where … in ‘ with the FIND_IN_SET function. And FIND_IN_SET will treat ‘@tmpvar’ as a list, then search for column “a” within the list.

Shuo Wang
Author: Shuo Wang

Leave a Reply

Your email address will not be published. Required fields are marked *