MySQL – Using User Defined Variables and `USE` Command

mariadbmariadb-10.1MySQL

I have some schemas, all with the same format. The format is: schema name, underscore, single digit (example: database_1).
How can I loop through all of these schemas easily? I tried SET @num = 1; USE database_@num; But it says

Unknown database 'database_@num'

I'm using MariaDB 10.1.30

Best Answer

The particular statement you want to run - USE - is not supported by dynamic SQL. I therefore suspect what you want to do is not currently possible.

If dynamic SQL did support the USE statement, you could have done:

MariaDB 10.1:

SET @sql = CONCAT('USE database_', @num);
PREPARE stmt FROM @sql; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;`

MariaDB 10.3:

EXECUTE IMMEDIATE CONCAT('USE database_', @num);

Both result in:

ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

The MariaDB knowledge base has a list of permitted statements that you can PREPARE. Effectively, this is a list of statements that can be used in dynamic SQL.

EDIT:

So instead I suppose you're left with options such as:

  1. Generate your statements as text -- un-roll your loop to a long list of statements, dump them to a .sql file and then SOURCE that file.
  2. Use a scripting language such as Python, bash, PHP etcetera. Do the loop in the scripting language. (Do not use prepared statements for the USE statement, obviously.)
Related Question