使用PHP生成器批量更新数据
- 使用PDO操作数据
- 生成器使用
- 随机暂停缓解数据库压力
- 批量更新数据
prepare($querySql);
$queryStmt->execute();
$result = $queryStmt->fetchAll(PDO::FETCH_ASSOC);
$queryStmt->closeCursor();
return $result;
};
do {
// 防止开发时死循环
if (DEBUG) {
++$testFor;
if ($testFor > $testNum) {
break;
}
}
// 记录开始时间
$begin = $getCurrentTime();
if ($result = $selectQuery($pdo, $tableName, $start, $everyRow)) {
// 定义生成器
$getGenerator = function ($data) {
foreach ($data as $key => $value) {
yield $key => $value;
}
};
// 遍历生成器,实现拼接执行SQL语句 1 web UU1%,U1% 2 wap UL%,UM% 3 iOS UA%,UUA% 4 Android UB%,UUB%
$generator = $getGenerator($result);
$sql = 'UPDATE '.$tableName.'
SET buy_from = CASE order_id';
$fromArray = ['U1'=>1,'UU1'=>1,'UM'=>2,'UL'=>2,'UA'=>3,'UUA'=>3,'UB'=>4,'UUB'=>4];
$orderIdStr = '';
foreach ($generator as $value) {
$order_sn = isset($value['order_sn']) ? trim($value['order_sn']) : false;
if ($order_sn) {
$prefixFrom = substr($order_sn, 0, 2); // 截取字符串
if($prefixFrom == 'UU'){ // 快速订单处理
$prefixFrom = substr($order_sn, 0, 3);
$fromBy = isset($fromArray[$prefixFrom]) ? $fromArray[$prefixFrom] : '';
}else{
$fromBy = isset($fromArray[$prefixFrom]) ? $fromArray[$prefixFrom] : '';
}
if ($fromBy) {
$orderId = $value['order_id'];
$orderIdStr .= $orderId.',';
$sql .= " WHEN {$orderId} THEN {$fromBy}";
}
}
}
//拼接SQL,处理大数据更新
$orderIdStrWhere = rtrim($orderIdStr, ",");
$sql .= ' END WHERE order_id IN ('.$orderIdStrWhere.')';
$count = 0;
if (!DEBUG) {
$stmt = $pdo->prepare($sql);
$stmt->execute();
$count = $stmt->rowCount();
$stmt->closeCursor();
}
$end = $getCurrentTime();
$spend = round($end - $begin,5);
$memory = memory_get_usage(); // 返回的单位是b,/1024得到kb,/(1024*1024)得到mb 1024*1024 = 1048576
$logContent = '序号:'.$number.' | 查询SQL:'.$querySql.' | 脚本执行时间为:'.$spend.' 秒 | 影响行数:'.$count.' | 消耗内存:'.($memory / 1048576).' mb';
$loger($logContent); // 记录日志
echo $logContent.'
';
}
$start += $everyRow; // 分页记录计数
++$number; //序号计数
// 随机暂停 0.2 - 0.99 秒 缓解并发情况下对DB压力
usleep(rand(200, 999) * 1000);
} while ($result = $selectQuery($pdo, $tableName, $start, $everyRow));
} catch (PDOException $e) {
die( "Error!: " . $e->getMessage());
}
网页题目:使用PHP生成器批量更新数据
浏览路径:http://myzitong.com/article/gjggoh.html