PHP MySQL 數據隨機重排遷移腳本實例

將舊表數據按隨機順序遷移至新表（或同表新增標記），常用於測試數據打亂、分表分庫、匿名化處理。本文提供分批處理、事務保護、進度持久化的完整腳本架構。

核心邏輯：分批 + 隨機排序 + 事務

<?php
// config.php
return [
    'db' => [
        'host' => 'localhost',
        'user' => 'root',
        'pass' => 'password',
        'name' => 'my_database',
    ],
    'batch_size' => 500,      // 每批處理行數
    'source_table' => 'info',
    'target_table' => 'info_bak',
    'marker_column' => 'is_migrated', // 0=未遷移, 1=已遷移
    'order_by' => 'RAND()',   // 隨機排序（大表慎用，見優化）
];

// migrate.php
require 'config.php';
$cfg = require 'config.php';

$mysqli = new mysqli($cfg['db']['host'], $cfg['db']['user'], $cfg['db']['pass'], $cfg['db']['name']);
if ($mysqli->connect_errno) die("DB連接失敗: " . $mysqli->connect_error);

$mysqli->begin_transaction();

try {
    $offset = isset($_GET['offset']) ? (int)$_GET['offset'] : 0;
    $limit  = $cfg['batch_size'];
    
    // 1. 選取未遷移數據（隨機順序）
    $sql = "SELECT * FROM `{$cfg['source_table']}` 
            WHERE `{$cfg['marker_column']}` = 0 
            ORDER BY {$cfg['order_by']} 
            LIMIT $offset, $limit";
    $result = $mysqli->query($sql);
    if (!$result) throw new Exception("查詢失敗: " . $mysqli->error);
    
    $rows = $result->fetch_all(MYSQLI_ASSOC);
    if (empty($rows)) {
        echo "遷移完成！共處理 $offset 條。";
        $mysqli->commit();
        exit;
    }
    
    // 2. 批量插入目標表
    $cols = array_keys($rows[0]);
    $colList = '`' . implode('`, `', $cols) . '`';
    $placeholders = '(' . implode(',', array_fill(0, count($cols), '?')) . ')';
    $allPlaceholders = implode(',', array_fill(0, count($rows), $placeholders));
    
    $insertSql = "INSERT INTO `{$cfg['target_table']}` ($colList) VALUES $allPlaceholders";
    $stmt = $mysqli->prepare($insertSql);
    if (!$stmt) throw new Exception("預處理失敗: " . $mysqli->error);
    
    // 綁定參數：動態構建類型字串與引用陣列
    $types = str_repeat('s', count($cols)); // 簡化：全部視為字串
    $bindParams = [$types];
    foreach ($rows as $row) {
        foreach ($cols as $col) {
            $bindParams[] = &$row[$col]; // 必須引用
        }
    }
    $stmt->bind_param(...$bindParams);
    $stmt->execute();
    if ($stmt->affected_rows !== count($rows)) {
        throw new Exception("插入行數不匹配: " . $stmt->affected_rows);
    }
    $stmt->close();
    
    // 3. 更新標記
    $ids = array_column($rows, 'id');
    $idList = implode(',', $ids);
    $updateSql = "UPDATE `{$cfg['source_table']}` SET `{$cfg['marker_column']}` = 1 WHERE `id` IN ($idList)";
    if (!$mysqli->query($updateSql)) {
        throw new Exception("標記更新失敗: " . $mysqli->error);
    }
    
    $mysqli->commit();
    
    // 4. 輸出進度並自動跳轉下一批（或 AJAX 輪詢）
    $nextOffset = $offset + $limit;
    echo "已處理 $nextOffset 條... 繼續下一批";
    // 或 header("Refresh: 1; url=?offset=$nextOffset");
    
} catch (Exception $e) {
    $mysqli->rollback();
    die("錯誤回滾: " . $e->getMessage());
}
?>

大表 ORDER BY RAND() 性能優化

ORDER BY RAND() 會生成臨時表並排序，百萬級表極慢。替代方案：

1. 主鍵範圍隨機：WHERE id >= (SELECT FLOOR(RAND() * (MAX(id)-MIN(id)+1)) + MIN(id)) ORDER BY id LIMIT N（需 ID 連續或近似均勻）。
2. 兩次查詢法：先 SELECT COUNT(*) 得總數，PHP 端 mt_rand(0, $total-1) 算 offset，再 LIMIT $offset, $batch（需無大量刪除導致空洞）。
3. 新增隨機列：表加 rand_sort FLOAT DEFAULT RAND()，建索引，遷移時 ORDER BY rand_sort，遷移後可丟棄。

進階：CLI 長連程序（避免 HTTP 超時）

<?php
// migrate_cli.php - 通過 crontab 或 supervisor 守護運行
set_time_limit(0);
ini_set('memory_limit', '256M');

$cfg = require 'config.php';
$mysqli = new mysqli(...);

$offset = 0;
while (true) {
    // 同 migrate.php 核心邏輯...
    if (empty($rows)) break;
    $offset += $limit;
    // 可選：每 100 批休眠 1 秒，降低主庫壓力
    if ($offset % 50000 === 0) sleep(1);
}
echo "全量遷移完成。";
?>