MySQL Prepare statement uses

Grammar

[SQL]View PlainCopy

1. PREPARE statement_name from sql_text/* definition * /
2. Execute statement_name [USING variable [, variable ...]]/* Execute preprocessing statement */
3. deallocate PREPARE statement_name/* Delete definition * /

Cases

[SQL]View PlainCopy

2. Mysql> PREPARE prod from "INSERT into Examlple VALUES (?,?)";
4. mysql> SET @p=' 1 ';
6. mysql> SET @q=' 2 ';
8. Mysql> EXECUTE prod USING @p,@q;
10. mysql> SET @name=' 3 ';
12. Mysql> EXECUTE prod USING @p,@name;
14. mysql> deallocate PREPARE prod;

1. Use a variable to make a table name: simply defining the variable with a set or Declare statement, and then directly as the SQL table name is not possible, MySQL will use the variable name as the table name. In other SQL databases as well, the workaround for MSSQL is to use the entire SQL statement as a variable, with the variable as the table name, and then invoke the statement with sp_executesql. This is not possible before mysql5.0, 5.0 introduced a completely new statement, can achieve similar sp_executesql function (only valid for procedure, function does not support dynamic query):

PREPARE stmt_name from preparable_stmt;
EXECUTE Stmt_name [USING @var_name [, @var_name] ...];
{deallocate | DROP} PREPARE Stmt_name;

In order to have a perceptual understanding, let's give a few small examples below:

[SQL]View PlainCopy

2. mysql> PREPARE stmt1 from ' SELECT SQRT (Pow (?, 2) + POW (?, 2)) as Hypotenuse ';
4. mysql> SET @a = 3;
6. mysql> SET @b = 4;
8. mysql> EXECUTE stmt1 USING @a, @b;
10. +------------+   
12. | hypotenuse |
14. +------------+   
16. | 5 |
18. +------------+   
20. mysql> deallocate PREPARE stmt1;
22. mysql> SET @s = ' SELECT SQRT (Pow (?, 2) + POW (?, 2)) as Hypotenuse ';
24. Mysql> PREPARE stmt2 from @s;
26. mysql> SET @a = 6;
28. mysql> SET @b = 8;
30. mysql> EXECUTE stmt2 USING @a, @b;
32. +------------+   
34. | hypotenuse |
36. +------------+   
38. | 10 |
40. +------------+   
42. mysql> deallocate PREPARE stmt2;

If your MySQL version is 5.0.7 or higher, you can also use it in the LIMIT clause, as in the following example:

[SQL]View PlainCopy

1. mysql> set @a=1;  
2. mysql> prepare stmt from < Span class= "string" > "SELECT&NBSP;*&NBSP;FROM&NBSP;TBL&NBSP;LIMIT&NBSP;?";    
3. mysql> execute  stmt using @a;   
4. mysql> set  @skip = 1; set  @numrows =5;   
5. mysql > prepare stmt from  " select * from tbl limit ?, ? ";    
6. mysql> execute stmt  using  @skip,  @numrows;   

Several points of note using PREPARE:
A:prepare stmt_name from preparable_stmt; A statement is predefined and assigned to Stmt_name, and tmt_name is case insensitive.
B: Even in the PREPARABLE_STMT statement? is represented by a string, and you do not need to be? Enclose them in quotation marks.
C: If the new PREPARE statement uses an existing stmt_name, the original will be released immediately! Even though this new PREPARE statement cannot be executed correctly because of an error.
The scope of D:prepare Stmt_name is that the current client connection session is visible.
E: To release a resource for a predefined statement, you can use the deallocate PREPARE syntax.
F:execute stmt_name syntax, if the stmt_name does not exist, an error will be raised.
G: If you do not explicitly invoke the deallocate PREPARE syntax to release resources when terminating a client connection session, the server will release it on its own.
H: In the predefined statements, CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, UPDATE, and most of the SHOW syntax are supported.
I:prepare statements can not be used for stored procedures, custom functions! But starting with MySQL 5.0.13, it can be used for stored procedures and is still not supported in functions!
For more information, refer to: http://www.jb51.net/article/7032.htm

MySQL Prepare statement uses