mysql的预处理函数

• 什么是预处理函数
• 预处理函数的用处
• 预处理函数的语法
• 预处理语句在存储过程中的使用
• 注意事项
• 使用示例

什么是预处理函数

预处理函数是指，在mysql服务器端预定义好需要处理的sql语句，语句中包含语句主体，以及使用?定义的参数占位符。
mysql的调用程序，例如mysql的客户端，或者连接mysql服务器的编程接口，例如java的jdbc，指定要调用的预处理函数，传入?标定的占位符参数，执行调用。

预处理函数的用处

1. 提高sql语句的执行效率，减少了对sql语句进行编译分析优化的过程。预处理函数的sql是定义在mysql服务器端的，提前对sql进行了编译，优化，分析，每次执行只需要传入相应的变量值进行替换即可。所以省去了对sql语句进行词法语义分析，语句优化，指定执行计划等步骤，提高了效率。
2. 可以防止sql注入。由于sql语句时提前编译好的，每次调用时传入相应的占位符参数，所以可以避免sql注入。

-- 此时，会有sql注入的问题，查出全量数据
SELECT * FROM mayikt_users WHERE id= 111 or 1=1;

--- 此时使用预处理，就只查出101的数据。防止了sql注入。
PREPARE user_stmt FROM 'SELECT * FROM mayikt_users WHERE id=?';

SET @id='101 or 1=1';
EXECUTE user_stmt USING @id;

DEALLOCATE PREPARE user_stmt;


EXECUTE user_stmt USING @id;

预处理程序的语法

1. 定义预处理的sql语句

-- 格式
prepare stmt_name from 'sql.....'
-- demo
prepare stmt from 'select * from user where id =?';

2. 执行预处理程序

-- 格式
EXECUTE stmt_name USEING @variable;
-- demo
set @id=1;
EXECUTE stmt USING  @id;

3. 释放预处理语句

-- 格式
DEALLOCATE prepare stmt_name;
-- demo
DEALLOCATE prepare stmt;

预处理在存储过程中的使用

1. 可以在存储过程中使用预处理语句。
2. 预处理语句的作用域是session，与存储过程是否结束无关。即在存储过程中定义的预处理语句，即使在存储过程结束时，如果没有执行DEALLOCATE，预处理语句不会释放。

CREATE PROCEDURE select_user(IN id INT)
BEGIN
  PREPARE stmt FROM 'SELECT * FROM mayikt_users WHERE id=?';
  SET @id=id;
  EXECUTE stmt using @id;
-- 此时不释放预处理语句
END;
--调用存储过程
SET @id=103;
call select_user(101);
--- 再执行预处理语句
--- 发现此时执行预处理语句还能成功
EXECUTE stmt USING  @id;
--如果此时再做释放预处理语句
DEALLOCATE prepare stmt;
--- 释放完预处理后，此时再做执行失败。
EXECUTE stmt USING @id;

3. 预处理语句，只能使用用户session级别的变量。存储过程的入参或者在存储过程中DECLARE的局部变量，都无法使用。

CREATE PROCEDURE select_user_local(IN id INT)
PREPARE stmt FROM 'SELECT * FROM mayikt_users WHERE id=?';
-----------------------------
EXECUTE stmt USING id; --执行错误，不能使用存储过程的入参作为参数执行
-------or----------------
DECLARE id_local INT;
SET id_local=id;
EXECUTE stmt USING id_local;--- 执行错误，无法使用局部变量作为参数执行。
------or-----------
SET @id=id;
EXECUTE stmt USING @id;---- 执行正确，但要注意，@id的作用域是会话级别的，即使出了存储过程也还存在，有可能在存储过程外被覆写。
-----or-----------
-- 不使用占位符，使用字符串拼接的方式
 SET @exec_sql=CONCAT("SELECT * FROM mayikt_users WHERE id=",id);
 PREPARE stmt FROM @exec_sql ;
 EXECUTE stmt ;
-----------------------------

注意事项

1. 表名与数据库名，不能作为预处理语句的条件参数。
   例如，此类用法，是错误的：

• 错误做法：使用表名与数据库名作为参数

SET @DATABASE_NAME='my_test';
SET @TABLE_NAME='`user`';
SET @EXEC_SQL="SELECT * FROM ?.?";
PREPARE stmt FROM @EXEC_SQL USING @DATABASE_NAME,@TABLE_NAME;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• 正确做法：使用参数拼接

SET @DATABASE_NAME='my_test';
SET @TABLE_NAME='`user`';
SET @EXEC_SQL=CONCAT("select * from ",@DATABASE_NAME,".",@TABLE_NAME);
PREPARE stmt FROM  @EXEC_SQL;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;

使用示例

1. 查询是否存在某个数据库。

SET @EXEC_QUERY_DATABASE_EXIST = "SELECT COUNT(A.SCHEMA_NAME) FROM(
SELECT SCHEMA_NAME 
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME=?) A
INTO @DATABSE_COUNT";
PREPARE stmt FROM  @EXEC_QUERY_DATABASE_EXIST;
EXECUTE stmt USING @DATABASE_NAME;
SELECT @DATABSE_COUNT;
DEALLOCATE PREPARE stmt;
--- 通过@DATABSE_COUNT的数量进行判断

2. 查询是否存在某张表

SET @CHECK_TABLE_COUNT="SELECT
	COUNT( A.TABLE_NAME ) 
FROM
	( SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ) A INTO @TABLE_COUNT";
PREPARE stmt FROM @CHECK_TABLE_COUNT;
EXECUTE stmt USING @DATABASE_NAME,@TABLE_NAME;
SELECT @TABLE_COUNT;
DEALLOCATE PREPARE stmt;