mysql insert into select插入表中的数据与select的数据不一样

最新推荐文章于 2024-09-23 19:39:45 发布

叫我泽西哥好吗

最新推荐文章于 2024-09-23 19:39:45 发布

阅读量5.5k

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/u013897685/article/details/52770025

版权

同一个动态拼接sql的写法：
使用变量拼接sql的受影响的行为6881（错误）,
不使用变量动态拼接sql的受影响的行为6916（正确）。
最后发现是用变量拼接sql中变量中有单引号影响了结果。
用双引号包住sql，问题消失。

错误：
set @monthTime=DATE_FORMAT(now(),'%Y%m');
set @clientTime_1=date_format(date_sub(now(),interval 1 day),'%Y%m%d00'); 
set @clientTime_2=date_format(date_sub(now(),interval 1 day),'%Y%m%d23');
set @clientTime_3=date_format(date_sub(now(),interval 3 day),'%Y%m%d00');
set @sql_into_table = concat('INSERT INTO game_center_finish_install_day_data_',@monthTime,'(
    num,
    serverTime,
    channel,
    version,
    packageName,
    appName,
    isCp
)SELECT
    count(a.id) AS num,
    LEFT (a.clientTime, 8) AS serverTime,
    a.channel as channel,
    a.version as version,
    a.packageName as packageName,
    a.appName as appName,
    a.isCp as isCp
FROM
    game_center_finish_install_log a
WHERE
    a.pageId != 1600 AND pageId > 0 and type=1
AND a.serverTime BETWEEN ',@clientTime_1,' AND ',@clientTime_2,' AND a.clientTime BETWEEN ',@clientTime_3,' AND ',@clientTime_2,
' GROUP BY
    LEFT(a.clientTime,8),
    a.version,
    a.channel,
    a.appName,
    a.packageName,
    a.isCp');
PREPARE sql_into_table FROM @sql_into_table;     
EXECUTE sql_into_table;

正确：
set @monthTime=DATE_FORMAT(now(),'%Y%m');
set @sql_into_table = concat("INSERT INTO game_center_finish_install_day_data_",@monthTime,"_copy (
    num,
    serverTime,
    channel,
    version,
    packageName,
    appName,
    isCp
)SELECT
    count(a.id) AS num,
    LEFT (a.clientTime, 8) AS serverTime,
    a.channel as channel,
    a.version as version,
    a.packageName as packageName,
    a.appName as appName,
    a.isCp as isCp
FROM
    game_center_finish_install_log a
WHERE
    a.pageId != 1600 AND pageId > 0 and type=1
AND a.serverTime BETWEEN date_format(date_sub(now(),interval 1 day),'%Y%m%d00') AND date_format(date_sub(now(),interval 1 day),'%Y%m%d23') AND a.clientTime BETWEEN date_format(date_sub(now(),interval 3 day),'%Y%m%d00') AND date_format(date_sub(now(),interval 1 day),'%Y%m%d23') GROUP BY
    LEFT(a.clientTime,8),
    a.version,
    a.channel,
    a.appName,
    a.packageName,
    a.isCp");
PREPARE sql_into_table FROM @sql_into_table;     
EXECUTE sql_into_table;