MySQL - 31预处理(绑定变量)

什么是预处理

从MySQL 4.1开始，就支持预处理语句(Prepared statement)，这大大提高了客户端和服务器端数据传输的效率。当创建一个预定义SQL时，客户端向服务器发送一个SQL语句的原型；服务器端接收到这个SQL语句后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄，以后每次执行这条SQL，客户端都指定使用这个句柄。

即时SQL(一次编译、一次运行)

1. 语法和词义的解析;
2. 优化SQL语句，制定执行计划;
3. 执行SQL语句并返回结果;

… …

1. 语法和词义的解析;
2. 优化SQL语句，制定执行计划;
3. 执行SQL语句并返回结果;

… …

预处理SQL(一次编译、多次运行)

1. 语法和词义的解析;
2. 优化SQL语句，制定执行计划;
3. 执行SQL语句并返回结果;

… …

1. 执行SQL语句并返回结果;

   … …

预处理的优势

高效执行重复SQL

• 在服务器端只需要解析一次SQL
• 在服务器端某些优化器的工作只需要执行一次，它会缓存一部分执行计划

更加安全：使用预处理语句，无须在应用程序中处理转义，也大大减少了SQL注入和攻击的风险。

预处理的基本使用

# 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;
# 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;      

实战案例

mysql> select * from employee;
+----+--------+------+--------+-------+
| id | name   | sex  | salary | dept  |
+----+--------+------+--------+-------+
|  1 | tom    | 1    |   5500 | deptA |
|  2 | lily1  | 0    |   4500 | deptA |
|  3 | lily2  | 0    |   4200 | deptB |
|  4 | lily3  | 1    |   7500 | deptB |
|  5 | lily4  | 0    |   8500 | deptA |
|  6 | lily5  | 1    |   6800 | deptB |
|  7 | lily6  | 1    |  12000 | deptA |
|  8 | lily7  | 1    |   3500 | deptA |
|  9 | lily8  | 1    |   6000 | deptC |
| 10 | lily9  | 1    |   8000 | deptC |
| 11 | lily10 | 0    |  10000 | deptA |
| 12 | lily11 | 0    |   4000 | deptA |
+----+--------+------+--------+-------+
12 rows in set (0.00 sec)

mysql> prepare sql_employee from 'select * from employee where name=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @name1='lily1';
Query OK, 0 rows affected (0.00 sec)

mysql> set @name2='lily2';
Query OK, 0 rows affected (0.00 sec)

mysql> set @name3='lily3';
Query OK, 0 rows affected (0.00 sec)

mysql> execute sql_employee using @name1;
+----+-------+------+--------+-------+
| id | name  | sex  | salary | dept  |
+----+-------+------+--------+-------+
|  2 | lily1 | 0    |   4500 | deptA |
+----+-------+------+--------+-------+
1 row in set (0.00 sec)

mysql> execute sql_employee using @name2;
+----+-------+------+--------+-------+
| id | name  | sex  | salary | dept  |
+----+-------+------+--------+-------+
|  3 | lily2 | 0    |   4200 | deptB |
+----+-------+------+--------+-------+
1 row in set (0.00 sec)

mysql> execute sql_employee using @name3;
+----+-------+------+--------+-------+
| id | name  | sex  | salary | dept  |
+----+-------+------+--------+-------+
|  4 | lily3 | 1    |   7500 | deptB |
+----+-------+------+--------+-------+
1 row in set (0.00 sec)

mysql> drop prepare sql_employee;
Query OK, 0 rows affected (0.00 sec)

mysql>