当前位置: 动力学知识库 > 问答 > 编程问答 >

Can I use a MySQL variable to name a table?

问题描述:

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!

网友答案:

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!

分享给朋友:
您可能感兴趣的文章:
随机阅读: