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?

I'm sorry, but I'd like to clarify this a bit: you intend to use some param to set a list of databases to query, right? — raina77ow
– raina77ow, Commented Jun 19, 2012 at 15:55
yes, that is correct. The result of the select into ignore_db will be a string like 'db1','db2','db3' etc. I need MySQL to treat the string literally. — dwjv
– dwjv, Commented Jun 19, 2012 at 15:59

RolandoMySQLDBA · Accepted Answer · 2012-06-19 17:01:11Z

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

Ah, of course. It's simple really. I was looking at the problem from inside the box. Thank you very much!

Collectives™ on Stack Overflow

Using string as comma separated query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related