预处理
-
概念
- 每个代码的段的执行都要经历
- 词法分析——语法分析——编译——执行
- 预编译一次,可以多次执行
- 用来解决一条SQL语句频繁执行的问题
- 每个代码的段的执行都要经历
-
语法
- 预处理语句:
prepare 预处理名字 from ‘sql语句’
- 执行预处理:
execute 预处理名字 [using 变量]
- MySQL中变量以@开头
- 通过set给变量赋值
- ?是位置占位符
- 预处理语句:
不带参数的预处理
MariaDB [sel]> prepare stmt from 'select * from grades';
# `Query OK, 0 rows affected (0.007 sec)`
# `Statement prepared`
MariaDB [sel]> execute stmt;
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Sunny | boy | 93 | 96 |
| Jerry | boy | 97 | 91 |
| Marry | girl | 95 | 94 |
| Tommy | boy | 98 | 94 |
+-------+------+---------+------+
# `4 rows in set (0.000 sec)`
带一个参数的预处理
MariaDB [sel]> prepare stmt from 'select * from grades where name=?';
# `Query OK, 0 rows affected (0.000 sec)`
# `Statement prepared`
MariaDB [sel]> delimiter //
MariaDB [sel]> set @name='Sunny';
-> execute stmt using @name //
# `Query OK, 0 rows affected (0.007 sec)`
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Sunny | boy | 93 | 96 |
+-------+------+---------+------+
# `1 row in set (0.008 sec)`
传递多个参数
MariaDB [sel]> prepare stmt from 'select * from grades where chinese<? and sex=?' //
# `Query OK, 0 rows affected (0.000 sec)`
# `Statement prepared`
MariaDB [sel]> set @chinese=97;
-> set @sex='boy';
-> execute stmt using @chinese,@sex //
# `Query OK, 0 rows affected (0.000 sec)`
# `Query OK, 0 rows affected (0.001 sec)`
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Sunny | boy | 93 | 96 |
+-------+------+---------+------+
# `1 row in set (0.007 sec)`