mysql使用数据库预处理_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)`