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

短命女 2022-10-02 12:00 216阅读 0赞

有些时候我们会动态组装一些sql，但是又不想创建一个存储过程，mysql正好有个方式支持的。

那就是Prepared SQL Statement Syntax

官方文档链接如下

[https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html][https_dev.mysql.com_doc_refman_8.0_en_sql-syntax-prepared-statements.html]

另外也参考了如下文章，感谢提供的思路。

[https://www.cnblogs.com/leodaxin/p/10340651.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

[https_dev.mysql.com_doc_refman_8.0_en_sql-syntax-prepared-statements.html]: https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html
[https_www.cnblogs.com_leodaxin_p_10340651.html]: https://www.cnblogs.com/leodaxin/p/10340651.html