MySQL prepare语句的SQL语法

MySQL prepare语法：

PREPARE statement_name FROM preparable_SQL_statement; /*定义*/
EXECUTE statement_name [USING @var_name [, @var_name] …]; /*执行预处理语句*/
{DEALLOCATE | DROP} PREPARE statement_name /*删除定义*/ ;

PREPARE语句用于预备一个语句，并指定名称statement_name，以后引用该语句。语句名称对大小写不敏感。preparable_stmt可以是一个文字字符串，也可以是一个包含了语句文本的用户变量。该文本必须表现为一个单一的SQL语句，而不是多个语句。在这语句里，‘?’字符可以被用于标识参数，当执行时，以指示数据值绑定到查询后。‘?’字符不应加引号，即使你想要把它们与字符串值结合在一起。参数标记只能用于数据值应该出现的地方，而不是SQL关键字，标识符，等等。

如果预语句已经存在，则在新的预语句被定义前，它会被隐含地删掉。

例如：

查看源代码

打印 帮助

1	mysql> prepare optimize_tables from "optimize table temp";

2	Query OK, 0 rows affected (0.00 sec)

3	Statement prepared

4

5	mysql> execute optimize_tables;

6	+-----------+----------+----------+----------+

7	| Table     | Op       | Msg_type | Msg_text |

8	+-----------+----------+----------+----------+

9	| test.temp | optimize | status   | OK       |

10	+-----------+----------+----------+----------+

11	1 row in set (0.37 sec)

12

13	mysql> deallocate prepare optimize_tables;

14	Query OK, 0 rows affected (0.00 sec)

15

16	--------------------------------------------------------------------------

17	mysql> prepare md5sum from 'select md5(?) AS md5sum';

18	Query OK, 0 rows affected (0.00 sec)

19	Statement prepared

20

21	mysql> set @a=111;

22	Query OK, 0 rows affected (0.00 sec)

23

24	mysql> set @b=222;

25	Query OK, 0 rows affected (0.00 sec)

26

27	mysql> execute md5sum using @a;

28	+----------------------------------+

29	| md5sum                           |

30	+----------------------------------+

31	| 698d51a19d8a121ce581499d7b701668 |

32	+----------------------------------+

33	1 row in set (0.00 sec)

34

35	mysql> execute md5sum using @b;

36	+----------------------------------+

37	| md5sum                           |

38	+----------------------------------+

39	| bcbe3365e6ac95ea2c0343a2395834dd |

40	+----------------------------------+

41	1 row in set (0.00 sec)

42

43	mysql> drop prepare md5sum;

44	Query OK, 0 rows affected (0.00 sec)

45

46	--------------------------------------------------------------------------------------

47

48	mysql> prepare update_table from "update users set password=password('aaa') where username='a'";

49	Query OK, 0 rows affected (0.00 sec)

50	Statement prepared

51

52	mysql> execute update_table;

53	Query OK, 0 rows affected (0.00 sec)

54	Rows matched: 1 Changed: 0 Warnings: 0

55

56	mysql> deallocate prepare update_table;

57	Query OK, 0 rows affected (0.00 sec)