Is there any way to use variables in table name place in MySQL?

Question

I want to use variable in table name position like: SELECT * FROM @targetTableName

However it makes error.

Is there any way to use variables in table name place in MySQL?

Answer 1

There are two reasons the query you show doesn't work.

1. Userd-defined variables interpolated into a query are treated as if you had use a string literal, not an identifier. The query you show would be like:

    SELECT * FROM 'mytable'
   

   That is of course not correct syntax. You can't select from a string literal.

2. Table names (and any other identifers) must be fixed at the time the query is parsed. So you can't make a query that names a table using a parameter, an expression, a subquery, or anything else. The table identifier must be plain and fixed before any data is read.

If you need to use a variable table name, you have to do it with dynamic SQL. That is, the whole query must be a string, which you can format any way you want it. Then parse that string as SQL at runtime.

Dynamic SQL is common. This is the way virtually all SQL is run from applications. If you use Java or Python or PHP or Go or any other language, you're probably using dynamic SQL.

If you run the query in a stored procedure, you have to use PREPARE and EXECUTE, after concatenating your variable into a string to format the query:

SET @sql = CONCAT('SELECT * FROM `', @targetTableName, '`');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;