如何在MySQL存储过程中使用动态SQL [英] How To have Dynamic SQL in MySQL Stored Procedure
问题描述
如何在MySQL存储过程中构建和使用动态sql?
How do you build and use dynamic sql in a MySQL stored procedure?
推荐答案
我不认为MySQL支持动态sql.您可以执行相似但不同的准备"语句.
I don't believe MySQL supports dynamic sql. You can do "prepared" statements which is similar, but different.
这里是一个例子:
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;
准备好的语句通常用于查看给定查询的执行计划.由于它们是通过 execute 命令执行的,并且可以将 sql 分配给变量,因此您可以近似得出一些与动态sql相同的行为.
The prepared statements are often used to see an execution plan for a given query. Since they are executed with the execute command and the sql can be assigned to a variable you can approximate the some of the same behavior as dynamic sql.
这是一个很好的链接关于此内容:
Here is a good link about this:
别忘了使用最后一行取消分配stmt
!
Don't forget to deallocate the stmt
using the last line!
祝你好运!
这篇关于如何在MySQL存储过程中使用动态SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!