Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | CREATE TABLE `t2` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `uniq_b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> select * from t2 where b>100; +------+------+------+ | a | b | c | +------+------+------+ | 123 | 234 | 345 | | 1234 | 2345 | 3456 | +------+------+------+ |
First step, I store the result values in a variable:
1 2 3 4 5 6 7 8 9 10 11 12 13 | set @tmpvar=(select<strong> group_concat(a)</strong> from t2 where b>100); mysql> select @tmpvar; +----------+ | @tmpvar | +----------+ | 123,1234 | +----------+ |
Secondly, update the table rows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> update t2 set c=300 where a in (@tmpvar); Query OK, <strong>1 row affected</strong> (0.14 sec) <strong>Rows matched: 1 Changed: 1</strong> Warnings: 0 mysql> select * from t2 where b>100; +------+------+------+ | a | b | c | +------+------+------+ | 123 | 234 | 300 | | 1234 | 2345 | 3456 | +------+------+------+ 2 rows in set (0.00 sec) |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | mysql> set @tmpvar=(select group_concat(a) from t2 where b>100); Query OK, 0 rows affected (0.00 sec) mysql> select @tmpvar; +----------+ | @tmpvar | +----------+ | 123,1234 | +----------+ 1 row in set (0.00 sec) mysql> update t2 set c=100 where a in (@tmpvar); Query OK, 0 rows affected (0.00 sec) <strong>Rows matched: 1 </strong> Changed: 0 Warnings: 0 mysql> update t2 set c=100 where a in (<strong>cast('123,1234' as char)</strong>); Query OK, 0 rows affected (0.01 sec) <strong>Rows matched: 1 </strong> Changed: 0 Warnings: 0 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | mysql> set @tmpvar=(select group_concat(a) from t2 where b>100); Query OK, 0 rows affected (0.00 sec) mysql> select @tmpvar; +----------+ | @tmpvar | +----------+ | 123,1234 | +----------+ 1 row in set (0.00 sec) mysql> set @sql=(<strong>concat("update t2 set c=555 where a in ( ",@tmpvar, " )" )</strong> ); Query OK, 0 rows affected (0.00 sec) mysql> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> execute stmt; Query OK, 2 rows affected (0.07 sec) <strong>Rows matched: 2 Changed: 2 </strong> Warnings: 0 mysql> select * from t2 where b>100; +------+------+------+ | a | b | c | +------+------+------+ | 123 | 234 | 555 | | 1234 | 2345 | 555 | +------+------+------+ 2 rows in set (0.00 sec) |
2. Use the FIND_IN_SET function
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | mysql> set @tmpvar=(select group_concat(a) from t2 where b>100); Query OK, 0 rows affected (0.00 sec) mysql> select @tmpvar; +----------+ | @tmpvar | +----------+ | 123,1234 | +----------+ 1 row in set (0.01 sec) mysql> update t2 set c=100 where FIND_IN_SET(a,@tmpvar); Query OK, <strong>2 rows affected</strong> (0.15 sec) <strong>Rows matched: 2 Changed: 2 </strong> Warnings: 0 mysql> select * from t2 where b>100; +------+------+------+ | a | b | c | +------+------+------+ | 123 | 234 | 100 | | 1234 | 2345 | 100 | +------+------+------+ 2 rows in set (0.00 sec) |
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.
