Use variables as table names in mysql stored procedures

Mysql stored procedures using variables as table names 1. use variables as table names: simply use the set or declare statement to define variables, and then directly use the SQL table name. mysql regards the variable name as the table name. This is also true in other SQL databases. The solution of mssql is to use the entire SQL statement as a variable, interspersed with variables as the table name, and then call this statement with sp_executesql. This does not work before mysql5.0. After MySQL 5.0, a new statement is introduced to achieve functions similar to sp_executesql (only effective for procedure, function does not support dynamic query ): SQL code PREPARE stmt_name FROM preparable_stmt; EXECUTE stmt_name [USING @ var_name [, @ var_name]...]; {DEALLOCATE | DROP} PREPARE stmt_name; to have a perceptual knowledge, let's give a few small examples: SQL code 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 @, @ B; + ------------ + | hypotenuse | + ------------ + | 10 | + ------------ + mysql> DEALLOCATE PREPARE stmt2; if your MySQL version is 5.0.7 or later, you can also use it in the LIMIT clause, for example: SQL code mysql> SET @ a = 1; mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT? "; Mysql> execute stmt using @ a; mysql> SET @ skip = 1; SET @ numrows = 5; mysql> 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, tmt_name 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. I: 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: SQL code 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'; Understand PREPARE usage, it is easy to use variables as table names. However, some other problems are also found in the actual operation process, such as the definition of variables, the usage of the declare and set @ var = value variables, and the variables passed in by parameters. After the test, it is found that the variables defined by set @ var = value are directly written in the string and converted as variables. The variables passed in by declare and parameters must be connected using CONCAT. The specific principle is not studied. EXECUTE stmt USING @ a; the variables after the statement USING can only be set @ var = value. The variables passed in by declare and parameters cannot. In addition, php also encountered many problems when calling the mysql stored PROCEDURE. PROCEDURE p can't return a result set in the given context.