Using string as comma separated query

Question

I'm trying to use a comma separated string as part of a query in a stored procedure but can't get it to work. I want the string to be:

'db1','db2','db3'

This is an example of the procedure (I've omitted lots of code for ease of reading):

CREATE PROCEDURE test(taskId int)
begin

declare done int default false;
declare ignore_db varchar(1024);

declare cur1 cursor for select schema_name from information_schema.schemata where schema_name not in (ignore_db);
declare continue handler for not found set done = true;

select value into ignore_db from dbw_parameters where upper(name)=upper('ignore db') and task_id = taskID;

select schema_name from information_schema.schemata where schema_name not in (ignore_db);
end;

I've tried:

set ignore_db=concat('\'',replace(ignore_db,',','\',\''),'\'');

but it just sees the result ('db1','db2','db3') as one string. I need it to treat the string as multiple databases.

Any ideas?

Answer 1

You do not need to add quotes to the list. Just use the LOCATE function

CREATE PROCEDURE test(taskId int)
begin

declare done int default false;
declare ignore_db varchar(1024);

declare cur1 cursor for select schema_name from information_schema.schemata where schema_name not in (ignore_db);
declare continue handler for not found set done = true;

select value into ignore_db from dbw_parameters where upper(name)=upper('ignore db') and task_id = taskID;

select schema_name from information_schema.schemata
where LOCATE(CONCAT(',',schema_name,','),CONCAT(',',ignore_db,',')) > 0;

end;

Here is a raw example of using LOCATE function in this way:

mysql> select LOCATE(',db1,',',db1,db2,db3,');
+---------------------------------+
| LOCATE(',db1,',',db1,db2,db3,') |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select LOCATE(',db2,',',db1,db2,db3,');
+---------------------------------+
| LOCATE(',db2,',',db1,db2,db3,') |
+---------------------------------+
|                               5 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select LOCATE(',db3,',',db1,db2,db3,');
+---------------------------------+
| LOCATE(',db3,',',db1,db2,db3,') |
+---------------------------------+
|                               9 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select LOCATE(',db4,',',db1,db2,db3,');
+---------------------------------+
| LOCATE(',db4,',',db1,db2,db3,') |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

BTW the reason I surround ignore_db with additional commas has to do with the database names itself

If you have databases with common prefixes, duplicate databases may appear that you did not intend. For exmaple, if ignore_db was db1,db2 while you have databases db1,db11,db2,db22,db111, then all 5 atabases would appear as a result. Thus, I added additional commas to both ignore_db and schema_name in the WHERE clause