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

阳光穿透心脏的1/2处 2022-12-29 02:18 231阅读 0赞

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70][]

### 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.释放

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70]: /images/20221120/a658a58db4eb4aff86338caf5cad328d.png