MySql语法问题

Question

为什么它能完美地工作：

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''residents'' ORDER BY ORDINAL_POSITION;';

PREPARE STMT FROM @columnQuery;
EXECUTE STMT;

但这不是：

DECLARE TableName VARCHAR(50);
SET @TableName = 'residents';

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''' + @TableName + ''' ORDER BY ORDINAL_POSITION;';

PREPARE STMT FROM @columnQuery;
EXECUTE STMT;

这一点也不是：

DECLARE TableName VARCHAR(50);
SET @TableName = 'residents';

SET @columnQuery = CONCAT('INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''', @TableName, ''' ORDER BY ORDINAL_POSITION;');

PREPARE STMT FROM @columnQuery;
EXECUTE STMT;

这也不是：

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;';

PREPARE STMT FROM @columnQuery;
EXECUTE STMT USING @TableName;

下面是存储过程中给我带来问题的部分：

CREATE PROCEDURE sp_ReturnSingleRowAsXml
(
  IN TableName VARCHAR(50)
)
BEGIN

DECLARE columnQuery VARCHAR(1000);

DROP TABLE IF EXISTS ColumnNames;

CREATE TEMPORARY TABLE ColumnNames (
  ID INT, ColumnName VARCHAR(50)
);

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''' + @TableName + ''' ORDER BY ORDINAL_POSITION;';

PREPARE STMT FROM @columnQuery;
EXECUTE STMT;

select * from ColumnNames;

END;

CALL sp_ReturnSingleRowAsXml('residents');

当select应该返回大约40行时，它却不返回任何内容。但是，如果我在@columnQuery中硬编码'residents‘这个词，我就会得到结果！是的，确实有一张名为“居民”的表格。

有时我会收到这个错误，有时我不会，这取决于我是在使用连接还是只是手动连接字符串：

MySQL数据库错误:您的SQL语法中有一个错误；请查看与您的MySQL服务器版本对应的手册，以了解要在第1行使用接近'NULL‘的正确语法

Answer 1

我能够让它在以下情况下工作：

CREATE PROCEDURE `sp_ReturnSingleRowAsXml`( 
  IN TableName VARCHAR(50) 
)
BEGIN 

DROP TABLE IF EXISTS ColumnNames; 

CREATE TEMPORARY TABLE ColumnNames ( 
  ID INT, ColumnName VARCHAR(50) 
); 
SET @tblName = TableName;
SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;'; 

PREPARE STMT FROM @columnQuery; 
EXECUTE STMT USING @tblName; 

select * from ColumnNames; 

END

Answer 2

试试这个：

DECLARE TableName VARCHAR(50); 
SET @TableName = 'residents'; 

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;'; 

PREPARE STMT FROM @columnQuery; 
EXECUTE STMT USING @TableName;