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

来源：互联网发布：戏剧打击乐器软件下载编辑：程序博客网时间：2024/05/18 05:17

同一个动态拼接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 isCpFROM    game_center_finish_install_log aWHERE    a.pageId != 1600 AND pageId > 0 and type=1AND 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 isCpFROM    game_center_finish_install_log aWHERE    a.pageId != 1600 AND pageId > 0 and type=1AND 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;

0 0