MySQL create string variable

Question

Is there any way to use a combination of strings for a MySQL variable?

I know if this is a string of just numbers it isn't a problem, but can I use a SET in this fashion?

mysql> select @SQL_BIND_DOCS;
+-----------------------------------------------------------------------+
| @SQL_BIND_DOCS                                                       |
+-----------------------------------------------------------------------+
| '1','2','3','4','5' |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select distinct doc from  documents doc where doc in (@SQL_BIND_DOCS);
Empty set (0.01 sec)

mysql> select distinct doc from documents doc where doc in ('1','2','3','4','5');
+---------------------------------+
| doc                             |
+---------------------------------+
| AA                              |
| BB                              |
| CC                              |
| DD                              |
| EE                              |
+---------------------------------+
5 rows in set (0.01 sec)

You can see here that there is no issue with the data -- any help provided would be appreciated.

I think you need to use a cursor. "User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT. This is true even if the variable is quoted..." — niry, Apr 11 '19 at 20:22
Even so, storing what seems to be an array [might be rough](https://stackoverflow.com/questions/12176709/how-can-i-simulate-an-array-variable-in-mysql). I might consider a different data format, such as [a string](https://stackoverflow.com/questions/16990392/mysql-select-in-clause-string-comma-delimited) or a [temporary table](https://stackoverflow.com/a/35309127/924299). See [Creating array variable in MySQL](https://stackoverflow.com/questions/20845503/) and [how to set an array as a mysql user variable](https://stackoverflow.com/questions/11424969/). — showdev, Apr 11 '19 at 21:06

score 1 · Accepted Answer · answered Apr 14 '19 at 20:42

You can use FIND_IN_SET:

select distinct doc from  documents doc where FIND_IN_SET(doc, @SQL_BIND_DOCS);

In your case, using IN does not work, because the variable is cast to 1.

References

FIND_IN_SET() vs IN()

difference between where_in and find_in_set

Why find_in_set works but IN clause

MySQL create string variable

1 Answers1