Mysql出现问题：什么是prepare语句解决方案

❤️作者主页：​​小虚竹​​

问题

什么是prepare语句？

解决方案

prepare语句介绍

prepare语句实际上就是一个预编译语句，先把SQL语句进行编译，且可以设定参数占位符（例如：?符号），然后调用时通过用户变量传入具体的参数值。prepare语句有三个步骤，预编译prepare语句，执行prepare语句，释放销毁prepare语句。
且performance_schema提供了针对prepare语句的监控记录。

优点

预编译语句的优势在于归纳为：

• 一次编译、多次运行，省去了解析优化等过程；
• 此外预编译语句能防止 SQL 注入。
• 解决无法传参问题：对于 LIMIT 子句中的值，必须是常量，不得使用变量，也就是说不能使用：SELECT * FROM TABLE LIMIT @skip, @numrows; 如此，就可以是用 PREPARE 语句解决此问题。

类似的：用变量传参做表名时，MySQL 会把变量名当做表名，这样既不是本意，也会是语法错误，在 SQL Server 的解决办法是利用字符串拼接穿插变量进行传参，再将整条 SQL 语句作为变量，最后是用 sp_executesql 调用该拼接 SQL 执行，而 Prepared SQL Statement 可谓异曲同工之妙。

统计表统计内容查看

use performance_schema;

select * from prepared_statements_instances;

没数据，看不了字段。难不了虚竹哥，顺便教下大家如何使用准备语句。

实战

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

第一步，使用PREPARE语句准备执行语句。该语句用于在给定了两个边的长度时，计算三角形的斜边。

第二步，声明了两个变量@a和@b;

第三步：第三，使用EXECUTE语句来执行变量@a和@b的准备语句。

这时我们就能看到统计信息了。

第四，我们使用DEALLOCATE PREPARE来释放资源。

DEALLOCATE PREPARE stmt1;

如上所示：

• prepare语句预编译：创建一个prepare语句。如果语句检测成功，则会在prepared_statements_instances表中新添加一行。
• prepare语句执行：检测执行了EXECUTE语句，会更新prepare_statements_instances表中对应的行信息。
• prepare语句解除资源分配：检测的prepare语句实例执行COM_STMT_CLOSE或SQLCOM_DEALLOCATE_PREPARE命令，同时将删除prepare_statements_instances表中对应的行信息。为了避免资源泄漏，请务必在prepare语句不需要使用的时候执行此步骤释放资源。

扩展

prepare支持的sql语句

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 | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
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

使用注意点

• stmt_name 作为 preparable_stmt 的接收者，唯一标识，不区分大小写。
• preparable_stmt 语句中的 ? 是个占位符，所代表的是一个字符串，不需要将 ? 用引号包含起来。
• 定义一个已存在的 stmt_name ，原有的将被立即释放，类似于变量的重新赋值。
• PREPARE stmt_name 的作用域是session级
• 可以通过 max_prepared_stmt_count 变量来控制全局最大的存储的预处理语句。

# 最多允许的准备语句数量
# max_prepared_stmt_count
show variables like 'max_prepared%';

• 为了避免资源泄漏，请务必在prepare语句不需要使用时候执行此步骤释放资源。

参考

​​mysql官方-prepared-statements​​​mysql-prepare用法
SQL进阶-查询优化- performance_schema系列五：数据库对象事件与属性统计（SQL 小虚竹)