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)`
上一篇:序列化与反序列化(1)Serializable —— Java原生态方法


下一篇:Java 什么时候使用static