How can I use user defined variables and `USE`?

Question

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

• Rick James: Having a bunch of 'identical' databases (or tables) is usually a bad way to design the schema. Rather than finding a solution to database_@num, let's discuss getting rid of the problem.
• kurdtpage: Can you suggest a good place to discuss such issues?
• Rick James: Start a new question. Explain the purpose of having database_nnn. Tell us what kind of data (shopping, scientific, time series, etc) that you have. Ask for performance advice on fetching from different databases due to the split you decided on.

Answer 1

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.)