SQL syntax of MySQLprepare statement _ MySQL

SQL syntax of MySQLprepare statement bitsCN.com

MySQL prepare syntax:

PREPAREStatement_nameFROMPreparable_ SQL _statement;/* definition */

EXECUTEStatement_name [USING @ Var_name [, @ var_name]...];/* Execute the preprocessing statement */

{DEALLOCATE | DROP} PREPAREStatement_name/* delete definition */;

The PREPARE statement is used to PREPARE a statement and specify the name statement_name. this statement will be referenced later. The statement name is case insensitive. Preparable_stmt can be a text string or a user variable containing the Statement Text. The text must be expressed as a single SQL statement, rather than multiple statements. In this statement ,'? The 'character can be used to identify a parameter. when executed, it indicates that the data value is bound to the query. '? 'Characters should not be enclosed in quotation marks, even if you want to combine them with string values. Parameter tags can only be used where data values should appear, rather than SQL keywords, identifiers, and so on.

If a pre-statement already exists, it is implicitly deleted before the new pre-statement is defined.

For example:

mysql> prepare optimize_tables from "optimize table temp";Query OK, 0 rows affected (0.00 sec)Statement preparedmysql> execute optimize_tables;+-----------+----------+----------+----------+| Table     | Op       | Msg_type | Msg_text |+-----------+----------+----------+----------+| test.temp | optimize | status   | OK       | +-----------+----------+----------+----------+1 row in set (0.37 sec)mysql> deallocate prepare optimize_tables;Query OK, 0 rows affected (0.00 sec)--------------------------------------------------------------------------mysql> prepare md5sum from 'select md5(?) AS md5sum';Query OK, 0 rows affected (0.00 sec)Statement preparedmysql> set @a=111;Query OK, 0 rows affected (0.00 sec)mysql> set @b=222;Query OK, 0 rows affected (0.00 sec)mysql> execute md5sum using @a;+----------------------------------+| md5sum                           |+----------------------------------+| 698d51a19d8a121ce581499d7b701668 | +----------------------------------+1 row in set (0.00 sec)mysql> execute md5sum using @b;+----------------------------------+| md5sum                           |+----------------------------------+| bcbe3365e6ac95ea2c0343a2395834dd | +----------------------------------+1 row in set (0.00 sec)mysql> drop prepare md5sum;Query OK, 0 rows affected (0.00 sec)--------------------------------------------------------------------------------------mysql> prepare update_table from "update users set password=password('aaa') where username='a'";Query OK, 0 rows affected (0.00 sec)Statement preparedmysql> execute update_table;Query OK, 0 rows affected (0.00 sec)Rows matched: 1 Changed: 0 Warnings: 0mysql> deallocate prepare update_table;Query OK, 0 rows affected (0.00 sec)

MySQL 5.0 and later versions support a new SQL syntax:

PREPARE stmt_name FROM preparable_stmt;EXECUTE stmt_name [USING @var_name [, @var_name] ...];{DEALLOCATE | DROP} PREPARE stmt_name;

Through it, we can implement sp_executesql similar to ms SQL to execute dynamic SQL statements!

It can also prevent injection attacks!

To have a perceptual knowledge,

Here are a few small examples:

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';mysql> SET @a = 3;mysql> SET @b = 4;mysql> EXECUTE stmt1 USING @a, @b;+------------+| hypotenuse |+------------+ | 5 |+------------+mysql> DEALLOCATE PREPARE stmt1;mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> PREPARE stmt2 FROM @s;mysql> SET @a = 6;mysql> SET @b = 8;mysql> EXECUTE stmt2 USING @a, @b;+------------+| hypotenuse |+------------+| 10 |+------------+mysql> DEALLOCATE PREPARE stmt2;

If your MySQL version is 5.0.7 or later, you can use it in the LIMIT clause, for example: mysql> SET @ a = 1; mysql>

PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?"; mysql> EXECUTE STMT USING @a;mysql> SET @skip=1; SET @numrows=5; phperz.commysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";

Mysql> execute stmt using @ skip, @ numrows; notes for using prepare:

A: PREPARE stmt_name FROM preparable_stmt;

Predefines a statement and assigns it to stmt_name, which is case insensitive.

B: even in the preparable_stmt statement? It represents a string, and you do not need? Enclosed in quotation marks.

C: If the new PREPARE statement uses an existing stmt_name, the original one will be released immediately! Even if the new PREPARE statement cannot be correctly executed due to an error.

D: the scope of PREPARE stmt_name is visible to the current client connection session.

E: to release resources of a predefined statement, use the deallocate prepare syntax.

F: In the EXECUTE stmt_name syntax, if stmt_name does not exist, an error is thrown.

G: If the deallocate prepare syntax is not explicitly called to release the resource when terminating the client connection session, the server will manually release the resource.

H: in predefined statements, create table, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW syntaxes are supported.

G: The PREPARE statement cannot be used in stored procedures. it is a custom function! However, MySQL 5.0.13 and later versions can be used in stored procedures and cannot be used in functions! The following is an example:

 CREATE PROCEDURE `p1`(IN id INT UNSIGNED,IN name VARCHAR(11))BEGIN lable_exit: BEGIN SET  @SqlCmd = 'SELECT * FROM tA '; IF id IS NOT NULL THEN SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE id=?');  PREPARE stmt FROM @SqlCmd; SET @a = id; EXECUTE stmt USING @a; LEAVE lable_exit; END IF;  IF name IS NOT NULL THEN SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE name LIKE ?');  PREPARE stmt FROM @SqlCmd; SET @a = CONCAT(name, '%'); EXECUTE stmt USING @a; LEAVE lable_exit; END IF;  END lable_exit;END; CALL `p1`(1,NULL);CALL `p1`(NULL,'QQ');DROP PROCEDURE `p1`;

Declare this as Reprinted

The above is the SQL syntax of MySQL prepare statement _ MySQL content. For more information, see PHP Chinese network (www.php1.cn )!