Mysql预编译动态拼接表名查询

最新推荐文章于 2025-05-14 13:50:09 发布

原创最新推荐文章于 2025-05-14 13:50:09 发布 · 3.8k 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mysql动态sql预编译

Mysql 专栏收录该内容

16 篇文章

订阅专栏

博客围绕deal_runnning表按年月分表的动态拼接表名查询展开。目标是执行动态拼接表名的SQL，但使用concat连接表名无效。最终给出解决办法，即采用MySQL的预编译动态SQL进行处理。

1.实现目标,现在对deal_runnning表进行按年月分表,需要动态拼接表名查询

2.需要执行此sql .表名要动态拼接,目标sql如下,使用concat连接表名无效.

SELECT
id,
bazaar_id,
(SELECT fmName FROM fm_boooth_shop_user WHERE fmId=bazaar_id) AS bazaar_name,
merchant_id,
(SELECT shop_name FROM shop WHERE id=merchant_id) AS merchant_name,
order_num,
deal_type,
order_type,
order_money,
pay_type,
pay_account,
create_time,
create_user,
(SELECT nick_name FROM sys_user WHERE user_id=create_user) AS user_name,
delete_status
FROM  table
WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 MINUTE);

3.解决办法,使用mysql的预编译动态sql处理

SET @baipddl =CONCAT('deal_runnning',DATE_FORMAT(NOW(),'%Y%m')); #1.先把表名拼接出来
SET @abc= CONCAT('SELECT id,bazaar_id,
(SELECT fmName FROM fm_boooth_shop_user WHERE fmId=bazaar_id) AS bazaar_name,
merchant_id,
(SELECT shop_name FROM shop WHERE id=merchant_id) AS merchant_name,
order_num,deal_type,order_type,order_money,pay_type,pay_account,create_time,create_user,
(SELECT nick_name FROM sys_user WHERE user_id=create_user) AS user_name,
delete_status FROM ', @baipddl); #2.再把查询语句前半段拼接起来
SET @bcd = CONCAT(@abc,' WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 MINUTE)'); #3.再把查询条件拼接起来
PREPARE stmt FROM @bcd; #4.预编译
EXECUTE stmt;  #5.执行
DEALLOCATE PREPARE stmt; #6.释放