预处理
概念
每个代码的段的执行都要经历
词法分析——语法分析——编译——执行
预编译一次,可以多次执行
用来解决一条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)`