<?phpheader('Content-Type: text/html; charset=utf-8');// 定义数据配置信息$host = '';$dbname = '';$user = '';$pass = '';$prefix = '';try { // 是否开启更新执行 define('DEBUG',true); // true 执行修改SQL false 反之， $testFor = 0; $testNum = 3; // 测试循环次数 如果开启执行 true 更新SQL，测试无效， //每次执行多少条数据 $everyRow = 1000; $start = 0; $number = 1; // 序号 //记录日志 $loger = function($content){ $file = '/tmp/sql.log'; $date = date('Y-m-d H:i:s'); file_put_contents($file, "[{$date}] | {$content}".PHP_EOL, FILE_APPEND | LOCK_EX); }; // 获取当前时间戳，精确到毫秒 $getCurrentTime = function () { list ($msec, $sec) = explode(" ", microtime()); return (float)$msec + (float)$sec; }; // 连接数据库$pdo = new PDO ("mysql:host={$host};dbname={$dbname}", $user, $pass); $tableName = $prefix.'order_info'; // 定义获取数据函数 $querySql = ''; $selectQuery = function ($pdo, $tableName, $start, $everyRow) { global $querySql; $querySql = "SELECT order_id,order_sn,buy_from FROM {$tableName} WHERE buy_from = 0 LIMIT {$start},{$everyRow}"; $queryStmt = $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.'<br/>'; } $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());}