如何在 MySQL 存储过程中使用动态 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/190776/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How To have Dynamic SQL in MySQL Stored Procedure
提问by Brian Boatright
How do you build and use dynamic sql in a MySQL stored procedure?
如何在 MySQL 存储过程中构建和使用动态 sql？
采纳答案by Jason Stevenson
I don't believe MySQL supports dynamic sql. You can do "prepared" statements which is similar, but different.
我不相信 MySQL 支持动态 sql。您可以执行类似但不同的“准备”语句。
Here is an example:
下面是一个例子：
mysql> PREPARE stmt FROM
-> 'select count(*)
-> from information_schema.schemata
-> where schema_name = ? or schema_name = ?'
;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt
-> USING @schema1,@schema2
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;
The prepared statements are often used to see an execution plan for a given query. Since they are executed with the executecommand and the sqlcan be assigned to a variable you can approximate the some of the same behavior as dynamic sql.
准备好的语句通常用于查看给定查询的执行计划。由于它们是使用execute命令执行的，并且可以将sql分配给一个变量，因此您可以近似某些与动态 sql 相同的行为。
Here is a good linkabout this:
这是一个很好的链接：
Don't forget to deallocate the stmtusing the last line!
不要忘记stmt使用最后一行解除分配！
Good Luck!
祝你好运！
回答by TimoSolo
After 5.0.13, in stored procedures, you can use dynamic SQL:
5.0.13之后，在存储过程中，可以使用动态SQL：
delimiter //
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
SET @s = CONCAT('SELECT ',col,' FROM ',tbl );
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
//
delimiter ;
Dynamic SQL does not work in functions or triggers. See the MySQL documentationfor more uses.
动态 SQL 在函数或触发器中不起作用。有关更多用途，请参阅MySQL 文档。
回答by Elcio
You can pass thru outside the dynamic statement using User-Defined Variables
您可以使用用户定义的变量在动态语句之外传递
Server version: 5.6.25-log MySQL Community Server (GPL)
mysql> PREPARE stmt FROM 'select "AAAA" into @a';
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 1 row affected (0.01 sec)
DEALLOCATE prepare stmt;
Query OK, 0 rows affected (0.01 sec)
mysql> select @a;
+------+
| @a |
+------+
|AAAA |
+------+
1 row in set (0.01 sec)
