如何在 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屋！