MySQL 原始查询 : using @var

Question

我正在尝试使用定义为 @var 的常量前缀重命名表列表:

SET @p='newprefix_';
RENAME TABLE `oldprefix_tablename1` TO CONCAT(@p, 'tablename1');
RENAME TABLE `oldprefix_tablename2` TO CONCAT(@p, 'tablename2');

这个语法是错误的，但我看到:

SELECT  CONCAT(@p, 'tablename'); //outputs newprefix_tablename

这里使用的正确方法/语法是什么？

Answer 1

您不能直接按照您尝试的方式进行操作。正如手册所说( 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.

您必须使用准备好的语句:

SET @p = 'newprefix_';
SET @s = CONCAT('RENAME TABLE `oldprefix_tablename1` to ', @p, 'tablename1');
PREPARE stmt FROM @s;
EXECUTE stmt;