具有局部变量的动态 MySQL
如何在 MySQL 数据库中使用动态 SQL 语句而不使用会话变量？
现在我有这样的代码（在MySQL存储过程中）：
(...)
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
(...)但我只想使用局部变量，这意味着我想用以下内容开始这个过程：
DECLARE TableName VARCHAR(32); DECLARE s VARCHAR(1024); DECLARE SelectedId INTEGER UNSIGNED; (...)
并且不要在任何地方使用@ char。有什么办法可以做到这一点吗？
How can I use dynamic SQL statements in MySQL database and without using session variables?
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; (...)
and do not use @ char anywhere. Is there any way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
抱歉，MySQL 中的准备语句是会话全局的。根据 http://dev.mysql。 com/doc/refman/5.1/en/sql-syntax-prepared-statements.html，“准备好的语句对于会话也是全局的。”
并且没有其他方法（除了准备好的语句）可以在 MySQL 5.x 中执行动态 SQL。
因此，您当然可以替换上面的“@s”，但据我所知，您仍然使用@SelectedId。
在 MySQL 6.x 中，计划添加一个“EXECUTE IMMEDIATE”语句来执行动态 SQL 的功能。请参阅http://forge.mysql.com/worklog/task.php?id =2793。
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."
And there's no other way (besides prepared statements) to execute dynamic SQL in MySQL 5.x.
So you can of course replace "@s" above, but AFAIK you're stuck with @SelectedId.
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.
上面的链接给出了未找到的页面。请参阅此处：
https://dev.mysql.com/doc/refman/5.7/ en/prepare.html
最后一段明确指出：
“
在存储程序上下文中准备的语句不能引用存储过程或函数参数或局部变量，因为它们在程序结束时超出范围，并且如果稍后在程序外部执行该语句，它们将不可用。作为解决方法，请改为参考用户定义的变量，这些变量也具有会话范围；
”
The link above gives a page not found. See here instead :
https://dev.mysql.com/doc/refman/5.7/en/prepare.html
The end para clearly states :
"
A statement prepared in stored program context cannot refer to stored procedure or function parameters or local variables because they go out of scope when the program ends and would be unavailable were the statement to be executed later outside the program. As a workaround, refer instead to user-defined variables, which also have session scope;
"