# 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;
# 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;
1、利用
字符串
定义预处理 SQL
(直角三角形计算)
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.00 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 stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)
2、利用
变量
定义预处理 SQL
(直角三角形计算)
mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE stmt2 FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> SET @c = 6;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @d = 8;
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE stmt2 USING @c, @d;
+------------+
| hypotenuse |
+------------+
| 10 |
+------------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt2;
Query OK, 0 rows affected (0.00 sec)
3、解决无法传参问题
我们知道，对于 LIMIT 子句中的值，必须是常量，不得使用变量，也就是说不能使用：SELECT * FROM TABLE LIMIT @skip, @numrows; 如此，就可以是用 PREPARE 语句解决此问题。
mysql> SET @skip = 100; SET @numrows = 3;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1 LIMIT @skip, @numrows;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@skip, @numrows' at line 1
mysql> PREPARE stmt3 FROM "SELECT * FROM t1 LIMIT ?, ?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt3 USING @skip, @numrows;
+-----+--------+
| a | filler |
+-----+--------+
| 100 | filler |
| 101 | filler |
| 102 | filler |
+-----+--------+
3 rows in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt3;
Query OK, 0 rows affected (0.00 sec)
如此一来，结合2中介绍的利用变量定义预处理 SQL 也就基本解决了传参时语法报错问题了，类似的：用变量传参做表名时，MySQL 会把变量名当做表名，这样既不是本意，也会是语法错误，在 SQL Server 的解决办法是
利用字符串拼接穿插变量进行传参
，再将整条 SQL 语句作为变量，最后是用
sp_executesql
调用该拼接 SQL 执行，而 Prepared SQL Statement 可谓异曲同工之妙。
mysql> SET @table = 't2';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @s = CONCAT('SELECT * FROM ', @table);
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE stmt4 FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt4;
+------+-------+-------+
| id | score | grade |
+------+-------+-------+
| 1 | 99 | A |
| 2 | 81 | B |
| 3 | 55 | D |
| 4 | 69 | C |
+------+-------+-------+
4 rows in set (0.00 sec)
mysql> DROP PREPARE stmt4;
Query OK, 0 rows affected (0.00 sec)
