mysql 不创建存储过程的方式动态组装sql

最新推荐文章于 2024-05-23 20:47:28 发布

原创最新推荐文章于 2024-05-23 20:47:28 发布

· 702 阅读

0 ·

版权

MySQL 专栏收录该内容

9 篇文章

订阅专栏

有些时候我们会动态组装一些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