7月 132010
从MySQL 5.0 开始，
支持了一个全新的SQL句法：
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] …];
{DEALLOCATE | DROP} PREPARE stmt_name;
通过它，我们就可以实现类似 MS SQL 的 sp_executesql 执行动态SQL语句！
同时也可以防止注入式攻击！
为了有一个感性的认识，
下面先给几个小例子：
|
1 |
mysql> <strong class="userinput"><code>PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';</code></strong><br />mysql> <strong class="userinput"><code>SET @a = 3;</code></strong><br />mysql> <strong class="userinput"><code>SET @b = 4;</code></strong><br />mysql> <strong class="userinput"><code>EXECUTE stmt1 USING @a, @b;</code></strong><br />+------------+<br />| hypotenuse |<br />+------------+<br />| 5 |<br />+------------+<br />mysql> <strong class="userinput"><code>DEALLOCATE PREPARE stmt1;</code></strong><br /> |
|
1 |
mysql> <strong class="userinput"><code>SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';</code></strong><br />mysql> <strong class="userinput"><code>PREPARE stmt2 FROM @s;</code></strong><br />mysql> <strong class="userinput"><code>SET @a = 6;</code></strong><br />mysql> <strong class="userinput"><code>SET @b = 8;</code></strong><br />mysql> <strong class="userinput"><code>EXECUTE stmt2 USING @a, @b;</code></strong><br />+------------+<br />| hypotenuse |<br />+------------+<br />| 10 |<br />+------------+<br />mysql> <strong class="userinput"><code>DEALLOCATE PREPARE stmt2;</code></strong><br /> |
|
1 |
<strong class="userinput"></strong><code><br /></code><pre class="programlisting">如果你的MySQL 版本是 5.0.7 或者更高的，你还可以在 LIMIT 子句中使用它，<br />示例如下：<br /> |
|
1 |
mysql> <code><strong>SET @a=1;</strong> |
mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";
mysql> EXECUTE STMT USING @a;
|
1 |
mysql><strong> <code>SET @skip=1; SET @numrows=5;</code></strong><br />mysql><strong> <code>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";</code></strong><br />mysql><strong> <code>EXECUTE STMT USING @skip, @numrows;</code></strong> |
|
1 |
</code><strong>使用 PREPARE 的几个注意点：<br /></strong>A：<br />PREPARE <em class="replaceable"><code>stmt_name</code></em> FROM <em class="replaceable"><code>preparable_stmt</code></em>;<br />预定义一个语句，并将它赋给 <em>stmt_name </em>，<em>stmt_name </em>是不区分大小写的。<br />B：<br /> 即使 <em>preparable_stmt</em> 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。<br />C：<br /> 如果新的 PREPARE 语句使用了一个已存在的 <em>stmt_name</em> ，那么原有的将被立即释放！<br /> 即使这个新的 PREPARE 语句因为错误而不能被正确执行。 |
|
1 |
D：<br /> PREPARE <em>stmt_name</em> 的作用域是当前客户端连接会话可见。<br />E：<br /> 要释放一个预定义语句的资源，可以使用 DEALLOCATE PREPARE 句法。 |
|
1 |
F： |
|
1 |
EXECUTE <em class="replaceable"><code>stmt_name</code></em> 句法中，如果 <em>stmt_name</em> 不存在，将会引发一个错误。 |
|
1 |
G： |
|
1 |
如果在终止客户端连接会话时，没有显式地调用 DEALLOCATE PREPARE 句法释放资源，服务器端会自己动释放它。<br />H： |
|
1 |
在预定义语句中，CREATE TABLE, <code class="literal">DELETE</code>, <code class="literal">DO</code>, <code class="literal">INSERT</code>, <code class="literal">REPLACE</code>, <code class="literal">SELECT</code>, <code class="literal">SET</code>, <code class="literal">UPDATE</code>, 和大部分的 <code class="literal">SHOW</code> 句法被支持。 |
|
1 |
<br />G： |
|
1 |
PREPARE 语句不可以用于存储过程，自定义函数！但从 MySQL 5.0.13 开始，它可以被用于存储过程，仍不支持在函数中使用！ |
|
1 |
下面给个示例： |
|
1 |
|
|
1 |
CREATE PROCEDURE `p1`(<br />IN id INT UNSIGNED,<br />IN name VARCHAR(11)<br />)<br />BEGIN<br /> lable_exit: BEGIN<br /> SET @SqlCmd = 'SELECT * FROM tA ';<br /> IF id IS NOT NULL THEN |
|
1 |
SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE id=?');<br /> PREPARE stmt FROM @SqlCmd;<br /> SET @a = id;<br /> EXECUTE stmt USING @a;<br /> LEAVE lable_exit;<br /> END IF;<br /> IF name IS NOT NULL THEN<br /> SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE name LIKE ?');<br /> PREPARE stmt FROM @SqlCmd;<br /> SET @a = CONCAT(name, '%');<br /> EXECUTE stmt USING @a;<br /> LEAVE lable_exit; <br /> END IF;<br /> END lable_exit;<br />END; |
|
1 |
|
|
1 |
CALL `p1`(1,NULL);<br />CALL `p1`(NULL,'QQ'); |
|
1 |
DROP PROCEDURE `p1`; |