每日MySQL之017：MySQL中的prepared statements

MySQL支持服务端的prepared statements

使用prepared statements的好处
1. 降低了每次SQL执行时的解析开销。特别是应用程序处理大量的几乎相同的SQL语句时，比如，select和delete语句只有where条件不同， update的语句只有set不同， insert语句只有values不同。
2. 降低SQL注入攻击的风险。

如何使用
主要由下面三个SQL语句组成：

PREPARE: prepare SQL statement

EXECUTE: 执行prepared statement

DEALLOCATE/DROP PREPARE：释放 prepared statement

下面举例2个例子说明，根据直角三角形的两条直角边的长度计算斜边的长度：
示例1：
mysql> PREPARE statement1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.05 sec)
Statement prepared

mysql> SET @a=3;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b=4;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE statement1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE statement1;
Query OK, 0 rows affected (0.00 sec)

示例2：
把statement作为变量传给PREPARE语句
mysql> SET @s='SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE statement2 FROM @s;

mysql> SET @a=6;
mysql> SET @b=8;
mysql> EXECUTE statement2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+

mysql> SET @c=3;
mysql> SET @d=4;
mysql> EXECUTE statement2 USING @c, @d;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+

mysql> DROP PREPARE statement2;

示例3：
将表名作为变量：
mysql> USE test1;
mysql> CREATE TABLE t1 (col1 int);
mysql> CREATE TABLE t2 (col1 int);
mysql> INSERT INTO t1 VALUES (2), (3), (5), (7);
mysql> INSERT INTO t2 VALUES (4), (6), (8);

mysql> SET @tablename = 't1';
mysql> SET @s = CONCAT('SELECT * FROM ', @tablename);

mysql> PREPARE statement3 FROM @s;
mysql> EXECUTE statement3;
+------+
| col1 |
+------+
|    2 |
|    3 |
|    5 |
|    7 |
+------+

mysql> SET @tablename = 't2';
mysql> EXECUTE statement3;
+------+
| col1 |
+------+
|    2 |
|    3 |
|    5 |
|    7 |
+------+

mysql> DEALLOCATE PREPARE statement3;
这里需要注意一点，由于tablename这个变量在prepare时已经传过来了，所以后来的“SET @tablename = 't2';”没有作用

附：示例3中用到了字符串连接函数CONCAT，学习一下这个函数的使用：CONCAT(str1,str2,...)，它的作用是将多个字符串连接起来，如果任一为NULL，则结果为NULL，会将数字转化为字符串
mysql> select CONCAT('My','S','QL');
+-----------------------+
| CONCAT('My','S','QL') |
+-----------------------+
| MySQL                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select CONCAT('My',NULL,'SQL');
+-------------------------+
| CONCAT('My',NULL,'SQL') |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (0.00 sec)

mysql> select CONCAT(3.1415926);
+-------------------+
| CONCAT(3.1415926) |
+-------------------+
| 3.1415926         |
+-------------------+
1 row in set (0.00 sec)