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