MySQL数据库之预处理

预处理

• 概念

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