thinkphp5 chunk 分块处理数据的坑
场景:
使用chunk方法进行分块查询写入数据,执行发现chunk分几条一次处理 数据库就插入几条,并没有return false;
源码分析如下
chunk 第3个参数是数组时,取的是分页,如果状态不断的更新,数据源是不断的在变化
第3个参数非数组,每次取的都是前面几条 ,数据源取的永远是前面的
通常第3个参数用到的数组是 多个字段排序,才会遇到此问题,非多个字段 慎用数组
代码举例如下:
注意此时有问题的是,chunk 第3个参数是个数组
-- count 44
SELECT COUNT(*) FROM agent WHERE `status` = 6;
-- count 0
SELECT COUNT(*) FROM agent WHERE `status` = 7;
sql语句如下
[ SQL ] SELECT `id`,`name` FROM `agent` WHERE `status` = 6 ORDER BY `id` LIMIT 0,30 [ RunTime:0.000926s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 31 [ RunTime:0.005106s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 32 [ RunTime:0.003842s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 34 [ RunTime:0.002873s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 35 [ RunTime:0.003258s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 36 [ RunTime:0.004567s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 37 [ RunTime:0.003386s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 38 [ RunTime:0.006443s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 41 [ RunTime:0.003224s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 42 [ RunTime:0.003093s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 43 [ RunTime:0.003712s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 44 [ RunTime:0.004631s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 45 [ RunTime:0.003607s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 46 [ RunTime:0.003660s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 48 [ RunTime:0.004149s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 50 [ RunTime:0.003582s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 51 [ RunTime:0.004171s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 52 [ RunTime:0.004329s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 53 [ RunTime:0.010809s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 55 [ RunTime:0.003725s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 58 [ RunTime:0.004220s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 64 [ RunTime:0.004714s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 65 [ RunTime:0.003782s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 68 [ RunTime:0.003905s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 76 [ RunTime:0.004706s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 78 [ RunTime:0.004264s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 81 [ RunTime:0.004535s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 83 [ RunTime:0.006476s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 88 [ RunTime:0.003775s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 91 [ RunTime:0.004364s ][ SQL ] UPDATE `agent` SET `status` = 7 WHERE `id` = 92 [ RunTime:0.005330s ][ SQL ] SELECT `id`,`name` FROM `agent` WHERE `status` = 6 ORDER BY `id` LIMIT 30,30 [ RunTime:0.002369s ]
此时查sql语句
说明状态6 改成7的还没有执行完,数据出现了漏处理的情况
出现问题的原因是
SELECT `id`,`name` FROM `agent` WHERE `status` = 6 ORDER BY `id` LIMIT 0,30
SELECT `id`,`name` FROM `agent` WHERE `status` = 6 ORDER BY `id` LIMIT 30,30
chunk第3个参数是数组,取的是LIMIT 0,30和 LIMIT 30,30,用的是分页当更改status状态后 他的数据源一直在变 ,
解决方案,一定要确保数据源保持不变!!!!!!即去掉status条件限制,放在循环里判断
\app\model\Agent::field('id, name,status')// ->where('status', 6) // 条件去掉 放到循环处理 ->chunk(30, function ($agentModel) { foreach ($agentModel as $info) { dump($info); if ($info->status == 6) { \app\model\Agent::where('id', $info['id'])->update(['status' => 7]); } } }, ['id']);
[ SQL ] SELECT `id`,`name`,`status` FROM `agent` ORDER BY `id` LIMIT 0,30 [ RunTime:0.001081s ][ SQL ] SELECT `id`,`name`,`status` FROM `agent` ORDER BY `id` LIMIT 30,30 [ RunTime:0.001012s ][ SQL ] SELECT `id`,`name`,`status` FROM `agent` ORDER BY `id` LIMIT 60,30 [ RunTime:0.000878s ][ SQL ] SELECT `id`,`name`,`status` FROM `agent` ORDER BY `id` LIMIT 90,30 [ RunTime:0.000963s ][ SQL ] SELECT `id`,`name`,`status` FROM `agent` ORDER BY `id` LIMIT 120,30 [ RunTime:0.001247s ]
通常第3个参数用到的数组是 多个字段排序,才会遇到此问题,非多个字段 慎用数组
代码示例
\app\model\Agent::field('id, name,status')// ->where('status', 6) // 条件去掉 放到循环处理 ->chunk(30, function ($agentModel) { foreach ($agentModel as $info) { dump($info); if ($info->status == 6) { \app\model\Agent::where('id', $info['id'])->update(['status' => 7]); } } }, ['sort desc', 'id asc']);
如果有id主键正常执行,第三个参数不传,会自动获取主键,是这样是没错的
\app\model\Agent::field('id, name') ->where('status', 5) // 注意这个有个status条件 ->chunk(30, function ($agentModel) { foreach ($agentModel as $info) { dump($info); \app\model\Agent::where('id', $info['id'])->update(['status' => 6]); } });
如果没有设置主键,第三个参数要传,为了安全起见,建议还是写上,不要偷懒!!!
上面两个id主键sql执行如下
[ SQL ] SELECT `id`,`name` FROM `agent` WHERE `status` = 5 ORDER BY `id` ASC LIMIT 30 [ RunTime:0.000991s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 31 [ RunTime:0.005297s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 32 [ RunTime:0.004962s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 34 [ RunTime:0.005891s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 35 [ RunTime:0.003951s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 36 [ RunTime:0.006202s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 37 [ RunTime:0.005032s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 38 [ RunTime:0.006677s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 41 [ RunTime:0.003708s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 42 [ RunTime:0.004749s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 43 [ RunTime:0.003820s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 44 [ RunTime:0.004658s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 45 [ RunTime:0.004239s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 46 [ RunTime:0.003897s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 48 [ RunTime:0.004970s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 50 [ RunTime:0.003220s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 51 [ RunTime:0.004782s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 52 [ RunTime:0.004196s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 53 [ RunTime:0.004342s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 55 [ RunTime:0.003299s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 58 [ RunTime:0.003533s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 64 [ RunTime:0.004330s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 65 [ RunTime:0.004645s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 68 [ RunTime:0.004522s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 76 [ RunTime:0.003577s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 78 [ RunTime:0.006653s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 81 [ RunTime:0.004538s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 83 [ RunTime:0.003604s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 88 [ RunTime:0.004038s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 91 [ RunTime:0.005194s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 92 [ RunTime:0.003052s ][ SQL ] SELECT `id`,`name` FROM `agent` WHERE `status` = 5 AND `id` > 92 ORDER BY `id` ASC LIMIT 30 [ RunTime:0.001586s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 99 [ RunTime:0.003963s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 108 [ RunTime:0.004367s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 110 [ RunTime:0.003883s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 115 [ RunTime:0.004952s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 134 [ RunTime:0.004294s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 145 [ RunTime:0.003549s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 171 [ RunTime:0.004260s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 175 [ RunTime:0.003893s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 182 [ RunTime:0.002691s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 195 [ RunTime:0.005611s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 197 [ RunTime:0.004149s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 199 [ RunTime:0.003191s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 202 [ RunTime:0.004697s ][ SQL ] UPDATE `agent` SET `status` = 6 WHERE `id` = 205 [ RunTime:0.004768s ][ SQL ] SELECT `id`,`name` FROM `agent` WHERE `status` = 5 AND `id` > 205 ORDER BY `id` ASC LIMIT 30 [ RunTime:0.001893s ]
这里的sql取的一直是前30条
SELECT `id`,`name` FROM `agent` WHERE `status` = 5 ORDER BY `id` ASC LIMIT 30
来源地址:https://blog.csdn.net/yunxixiao/article/details/126399181
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341