I've got a workflow that requires me to keep a bunch of tables with different names like
You get the idea.
Let's say that I've just created the table
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");
@dt_ncv evaluates to the string
But I can't figure out how (or if it's possible) to use this variable to reference the table.
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!
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.
I looked through my old MySQL notes and found a way to do it:
SET @dt_ncv = CONCAT((SELECT DATE_FORMAT(CURDATE(), '%Y_%m_%d')),"_ncvoter68"); SET @cntstmt = CONCAT_WS(" ", "SELECT count(*) FROM",@dt_ncv); PREPARE ncv_count_stmt FROM @cntstmt; EXECUTE ncv_count_stmt;
It's not pretty and it's not concise, but it works!