MySQL database stored procedure dynamic table creation (PREPARE)

2. PREPARE statement_name from sql_text/* definition */
4. EXECUTE statement_name [USING variable [, variable ...]/* Run pre-processing statement */
6. deallocate PREPARE statement_name/* Delete definition */

This is used in my project and used as a reference:

DELIMITER $ $DROP PROCEDURE IF EXISTS ' gpsdata '. ' Sp_test ' $ $CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_test ' ( Gpstime_ varchar (gpsname_), Gpsinfo_ varchar (begindeclare tbname varchar) DEFAULT  ' 0 ';D Eclare v_sql varchar (1024x768) DEFAULT ' 0 '; SET v_sql=concat (' select * from  ', Tbname, ' where Gpsname = ', gpsname_, '  ORDER by gpstime desc limit 1 '); SET @lastdata = V_sql; PREPARE lastdata from @lastdata;   EXECUTE lastdata;deallocate PREPARE lastdata;  Select V_sql; end$ $DELIMITER;

Several points of note using PREPARE:
A:prepare stmt_name from preparable_stmt;


Define a statement in advance and assign it to Stmt_name, Stmt_name is not distinguished by uppercase or lowercase.


B: Even in the PREPARABLE_STMT statement? Represents a string, and you do not need to. Include them with the quotes.


C: Assuming that the new PREPARE statement uses an existing stmt_name, the original will be released immediately!


Even though this new PREPARE statement cannot be run correctly due to 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 pre-defined statement, you can use the deallocate PREPARE syntax.

In F:execute stmt_name syntax, it is assumed that Stmt_name does not exist. An error will be raised.

G: Assume that the client connection session is terminated. Without explicitly invoking the deallocate PREPARE syntax to release the resource, the server side will release it itself.


H: In the pre-defined statement. 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 (more than 5.0 can be used), their own definition of functions!

But from the beginning of MySQL 5.0.13, it can be used for stored procedures, still do not support the use of functions!

MySQL database stored procedure dynamic table creation (PREPARE)