动态MySQL与局部变量 [英] Dynamic MySQL with local variables

问题描述

如何在MySQL数据库中使用动态SQL语句，而不使用会话变量？

How can I use dynamic SQL statements in MySQL database and without using session variables?

现在我有这样一个代码（在MySQL存储过程中） p>

Right now I have such a code (in MySQL stored procedure):

(...)
DECLARE TableName VARCHAR(32);
SET @SelectedId = NULL;
SET @s := CONCAT("SELECT Id INTO @SelectedId FROM ", TableName, " WHERE param=val LIMIT 1");
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF ISNULL(@SelectedId) THEN 
(...)

但是我只想使用局部变量，意味着我想开始这个过程：

But I'd like to use only local variables, that means I'd like to start this procedure with:

DECLARE TableName VARCHAR(32);
DECLARE s VARCHAR(1024);
DECLARE SelectedId INTEGER UNSIGNED;
(...)

，不要在任何地方使用@ char。有没有办法这样做？

and do not use @ char anywhere. Is there any way to do this?

推荐答案

对不起，MySQL中的准备语句是会话全局的。根据 http：//dev.mysql。 com / doc / refman / 5.1 / en / sql-syntax-prepared-statements.html ，准备好的语句也是会话的全局。

Sorry, prepared statements in MySQL are session-global. According to http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html, "A prepared statement is also global to the session."

除了准备的语句之外，MySQL 5.x中还没有其他方式执行动态SQL。

And there's no other way (besides prepared statements) to execute dynamic SQL in MySQL 5.x.

所以你可以在上面替换@s，但AFAIK你坚持使用@SelectedId。

So you can of course replace "@s" above, but AFAIK you're stuck with @SelectedId.

在MySQL 6.x中，有一个计划的功能将添加一个将执行动态SQL的EXECUTE IMMEDIATE语句。请参阅 http://forge.mysql.com/worklog/task.php?id = 2793 。

In MySQL 6.x, there is a feature planned which will add an "EXECUTE IMMEDIATE" statement which will execute dynamic SQL. See http://forge.mysql.com/worklog/task.php?id=2793.

这篇关于动态MySQL与局部变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！