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
– niry, Commented Apr 11, 2019 at 20:22
Even so, storing what seems to be an array might be rough. I might consider a different data format, such as a string or a temporary table. See Creating array variable in MySQL and how to set an array as a mysql user variable. — showdev
– showdev, Commented Apr 11, 2019 at 21:06

Jannes Botis · Accepted Answer · 2019-04-14 20:42:35Z

1

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

answered Apr 14, 2019 at 20:42

Jannes Botis

11.2k3 gold badges24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL create string variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related