<?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());}使用PHP生成器批量更新数据
原文地址：http://blog.51cto.com/9412490/2299922