mysql 平台查询定义变量_查询中的mysql用户定义变量

使用预备陈述：

SET @tablename = 'mytable';

SET @query = CONCAT('SELECT * FROM ', @mytable);

PREPARE stmt FROM @query;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

如果您的实际查询要复杂得多,您可以在一些存储函数中包装查询的构建：

DELIMITER //

CREATE FUNCTION GetQuery(tableName VARCHAR(50)) RETURNS VARCHAR(100)

BEGIN

DECLARE finalQuery VARCHAR(100);

SET finalQuery = CONCAT('SELECT * FROM ', tableName);

-- do something fancier with finalQuery here

RETURN finalQuery;

END //

DELIMITER ;

SET @query = GetQuery('mytable');

PREPARE stmt FROM @query;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

更好的是：在存储过程中执行所有操作：

DELIMITER //

CREATE PROCEDURE QueryTable(tableName VARCHAR(50))

BEGIN

SET @finalQuery = CONCAT('SELECT * FROM ', tableName);

PREPARE stmt FROM @finalQuery;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET @finalQuery = NULL;

END //

DELIMITER ;

CALL QueryTable('mytable');