SQL syntax for MySQL prepare statements
- 2020-05-12 06:22:22
- OfStack
MySQL prepare grammar:
PREPARE statement_name FROM preparable_SQL_statement; / * * / defined
EXECUTE statement_name [USING @var_name [, @var_name]...] ; /* execute preprocessing statement */
{DEALLOCATE | DROP} PREPARE statement_name /* delete definition */;
The PREPARE statement is used to prepare a statement, specify the name statement_name, and reference it later. Statement names are case-insensitive. preparable_stmt can be either a literal string or a user variable that contains the text of a statement. The text must appear as a single 1 SQL statement, not as multiple statements. In this sentence, '? The 'character can be used to identify parameters, when executed, to indicate that the data value is bound to the query. '? 'characters should not be quoted, even if you want to combine them with a string value of 1. Parameter markers can only be used where data values should appear, not SQL keywords, identifiers, and so on.
If the prestatement already exists, it is implicitly deleted before the new prestatement is defined.
Every time I read someone else's, I'll write it down myself today, so I won't have to read anyone else's
grammar
PREPARE statement_name FROM sql_text /* definition */
EXECUTE statement_name [USING variable [,variable...]] /* execute the preprocessing statement */
DEALLOCATE PREPARE statement_name /* delete definition */
case
Such as:
mysql > prepare optimize_tables from "optimize table temp";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql > 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 prepared
mysql > 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 prepared
mysql > execute update_table;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql > deallocate prepare update_table;
Query OK, 0 rows affected (0.00 sec)
Starting with MySQL 5.0, a new SQL syntax is supported:
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;
With it, we can implement sp_executesql like MS SQL to execute dynamic SQL statements!
It also prevents injection attacks!
In order to have a perceptual understanding,
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 version of MySQL is 5.0.7 or higher, you can also 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.com
mysql > PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
mysql > EXECUTE STMT USING @skip, @numrows; A few tips for using PREPARE:
A: PREPARE stmt_name FROM preparable_stmt;
Define one statement and assign it to stmt_name, stmt_name is case-insensitive.
B: even if preparable_stmt statement? What it represents is 1 string, you don't need to put? Put it in quotation marks.
C: if the new PREPARE statement USES an existing stmt_name, the existing stmt_name statement will be released immediately! Even if the new PREPARE statement cannot be executed correctly because of an error.
D: the scope of PREPARE stmt_name is visible to the current client connection session.
E: to release a resource for a predefined statement, use the DEALLOCATE PREPARE syntax.
F: in EXECUTE stmt_name syntax, if stmt_name does not exist, an error is raised.
G: if the client connection session is terminated without explicitly calling DEALLOCATE PREPARE syntax to release the resource, the server will automatically release it.
H: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most of the SHOW syntax are supported in predefined statements.
G: PREPARE statements cannot be used with stored procedures, custom functions! But starting with MySQL 5.0.13, it can be used for stored procedures, and it's still not supported in functions! Here's 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 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 NOT THEN SET = 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 `;
PREPARE statement_name FROM preparable_SQL_statement; / * * / defined
EXECUTE statement_name [USING @var_name [, @var_name]...] ; /* execute preprocessing statement */
{DEALLOCATE | DROP} PREPARE statement_name /* delete definition */;
The PREPARE statement is used to prepare a statement, specify the name statement_name, and reference it later. Statement names are case-insensitive. preparable_stmt can be either a literal string or a user variable that contains the text of a statement. The text must appear as a single 1 SQL statement, not as multiple statements. In this sentence, '? The 'character can be used to identify parameters, when executed, to indicate that the data value is bound to the query. '? 'characters should not be quoted, even if you want to combine them with a string value of 1. Parameter markers can only be used where data values should appear, not SQL keywords, identifiers, and so on.
If the prestatement already exists, it is implicitly deleted before the new prestatement is defined.
Every time I read someone else's, I'll write it down myself today, so I won't have to read anyone else's
grammar
PREPARE statement_name FROM sql_text /* definition */
EXECUTE statement_name [USING variable [,variable...]] /* execute the preprocessing statement */
DEALLOCATE PREPARE statement_name /* delete definition */
case
mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";
mysql> SET @p='1';
mysql> SET @q='2';
mysql> EXECUTE prod USING @p,@q;
mysql> SET @name='3';
mysql> EXECUTE prod USING @p,@name;
mysql> DEALLOCATE PREPARE prod;
Such as:
mysql > prepare optimize_tables from "optimize table temp";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql > 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 prepared
mysql > 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 prepared
mysql > execute update_table;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql > deallocate prepare update_table;
Query OK, 0 rows affected (0.00 sec)
Starting with MySQL 5.0, a new SQL syntax is supported:
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;
With it, we can implement sp_executesql like MS SQL to execute dynamic SQL statements!
It also prevents injection attacks!
In order to have a perceptual understanding,
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 version of MySQL is 5.0.7 or higher, you can also 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.com
mysql > PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
mysql > EXECUTE STMT USING @skip, @numrows; A few tips for using PREPARE:
A: PREPARE stmt_name FROM preparable_stmt;
Define one statement and assign it to stmt_name, stmt_name is case-insensitive.
B: even if preparable_stmt statement? What it represents is 1 string, you don't need to put? Put it in quotation marks.
C: if the new PREPARE statement USES an existing stmt_name, the existing stmt_name statement will be released immediately! Even if the new PREPARE statement cannot be executed correctly because of an error.
D: the scope of PREPARE stmt_name is visible to the current client connection session.
E: to release a resource for a predefined statement, use the DEALLOCATE PREPARE syntax.
F: in EXECUTE stmt_name syntax, if stmt_name does not exist, an error is raised.
G: if the client connection session is terminated without explicitly calling DEALLOCATE PREPARE syntax to release the resource, the server will automatically release it.
H: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most of the SHOW syntax are supported in predefined statements.
G: PREPARE statements cannot be used with stored procedures, custom functions! But starting with MySQL 5.0.13, it can be used for stored procedures, and it's still not supported in functions! Here's 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 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 NOT THEN SET = 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 `;