MySQL 8.0 prepare statement 资料

[dongzl]

1 mysql 官方文档

from https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html
prepared statement 有如下特征：

• 1 二进制协议通信
• 2 sql 语句使用占位符
  • 可设置参数的部分有
    • select 的 where
    • update 的 set 部分
    • insert 的 values 部分
  • 优点如下
    • 1 不用每次收到 SQL 都进行一次解析，减少解析次数
    • 2 减少 SQL 注入风险
• 3 prepare、execute、deallocate prepare
  • prepare 标志 preparement 的开始；
  • execute 执行 preparement
  • deallocate prepare 释放 preparement
• 4 如果 prepare statement 附属于一个客户端 session，则 session 被销毁时，则这个 prepare statement 也会被释放
• 5 如果 prepare statement 可以附属于一个 global session，一般是在存储过程中创建此类 prepare statement
• 6 为了防止用户创建过多 prepare statement，可以通过 max_prepared_stmt_count 系统变量设定上限。如果这个值为 0，则不能使用 prepare statement。
• 7 并不是所有的 sql 都可以出现在 prepare statement 中，其有限子集如下

ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
  | LOGS | STATUS | MASTER | SLAVE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE}
REVOKE
SELECT
SET
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE

• 8 一些诊断式的 sql 也不能出现在 prepare statement 中

SHOW WARNINGS, SHOW COUNT(*) WARNINGS

SHOW ERRORS, SHOW COUNT(*) ERRORS

Statements containing any reference to the warning_count or error_count system variable.

这些 prepare statement 不支持的 sql，存储过程也不支持。

• 9 select 中 limit 的参数可以进行 参数化
• 10 prepare statement 中不能出现 以 分号 分割的 multi-statements
• 11

2 命令行示例

示例1：prepare 后面直接跟着 sql statement

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

示例2: 把 sql text 作为 user variable，然后赋值为 prepare

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;

示例3：仍然是 sql text 作为 user variable，但是可以动态选择一个表名作为 prepament statement 的一部分

mysql> USE test;
mysql> CREATE TABLE t1 (a INT NOT NULL);
mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';
mysql> SET @s = CONCAT('SELECT * FROM ', @table);

mysql> PREPARE stmt3 FROM @s;
mysql> EXECUTE stmt3;
+----+
| a  |
+----+
|  4 |
|  8 |
| 11 |
| 32 |
| 80 |
+----+

mysql> DEALLOCATE PREPARE stmt3;

3 prepare statement

from https://dev.mysql.com/doc/refman/8.0/en/prepare.html
语法格式

PREPARE stmt_name FROM preparable_stmt

• stmt_name 大小写不敏感
• 可参数化的地方，以 ? 替代，其中不能出现引号，即便你想使用的是字符串变量也不行
• 如果 stmt_name 前面被已经被绑定了另一个 prepare statement，则再次被使用时，前面的 prepare statement 会被释放掉；
• 一个 session 创建的 prepare stmt，另一个 session 无法使用；

4 execute statement

from https://dev.mysql.com/doc/refman/8.0/en/execute.html
语法格式

EXECUTE stmt_name
    [USING @var_name [, @var_name] ...]

• 如果 stmt_name 中有参数，则必须在 using 中指定参数的值；
• 同一个 stmt_name 可以被 execute 多次；

5 deallocate prepare statement

from https://dev.mysql.com/doc/refman/8.0/en/deallocate-prepare.html
语法格式

{DEALLOCATE | DROP} PREPARE stmt_name

• 创建超过 max_prepared_stmt_count 数量的 stmt 而不释放，则系统会报错