有些时候我们会动态组装一些sql,但是又不想创建一个存储过程,mysql正好有个方式支持的。
那就是Prepared SQL Statement Syntax
官方文档链接如下
https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html
另外也参考了如下文章,感谢提供的思路。
https://www.cnblogs.com/leodaxin/p/10340651.html
简单来说有两种方式
The first example shows how to create a prepared statement by using a string literal to supply the text of the statement:
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;
The second example is similar, but supplies the text of the statement as a user variable:
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;
我这边使用的第二种方式,因为表名是动态的,没有参数,需要根据当前时间取分表名称。
set @s = concat('select * from table-',date_formate(now(),'%Y'%m'));
prepare st from @s;
execute st;
deallocate prepare st;
允许使用的关键字
SQL Syntax Allowed in Prepared Statements
The following SQL statements can be used as prepared statements:
ALTER TABLE ALTER USER ANALYZE TABLE CACHE INDEX CALL CHANGE MASTER CHECKSUM {TABLE | TABLES} COMMIT {CREATE | DROP} INDEX {CREATE | RENAME | DROP} DATABASE {CREATE | DROP} TABLE {CREATE | RENAME | DROP} USER {CREATE | DROP} VIEW DELETE DO FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS | MASTER | SLAVE | USER_RESOURCES} GRANT INSERT INSTALL PLUGIN KILL LOAD INDEX INTO CACHE OPTIMIZE TABLE RENAME TABLE REPAIR TABLE REPLACE RESET {MASTER | SLAVE} REVOKE SELECT SET SHOW {WARNINGS | ERRORS} SHOW BINLOG EVENTS SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW} SHOW {MASTER | BINARY} LOGS SHOW {MASTER | SLAVE} STATUS SLAVE {START | STOP} TRUNCATE TABLE UNINSTALL PLUGIN UPDATE