Can I use a MySQL variable to name a table?

Question

I've got a workflow that requires me to keep a bunch of tables with different names like

2012_10_15_ncvoter68
2012_11_15_ncvoter68
2012_12_15_ncvoter68

You get the idea.

Let's say that I've just created the table 2012_12_15_ncvoter68.

I would like to query this table using a variable. E.g., I could define a variable:

SET @dt_ncv = CONCAT((SELECT DATE_FORMAT(CURDATE(), '%Y_%m_%d')),"_ncvoter68");

The variable @dt_ncv evaluates to the string 2012_12_15_ncvoter68.

But I can't figure out how (or if it's possible) to use this variable to reference the table.

The query:

SELECT count(*) FROM @dt_ncv;

simply give a SQL syntax error.

I've tried playing with prepared statements but that didn't seem to help.

I hope some of you MySQL gurus can help!

Answer 1

No, you can't do that.

You'll have to handle the variable substitutions in a procedural language.

As the manual says (http://dev.mysql.com/doc/refman/5.1/en/user-variables.html)

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT.