需將 8.5 萬筆資料打亂重排,單次查詢會超時/記憶體溢出。本文採用分批游標 + 元刷新跳轉策略,逐批讀取、寫入新表、標記已處理,並自動跳轉下一批,適合無 CLI 權限的虛擬主機環境。
<?php
// rand.php
$s = (int)($_GET['s'] ?? 0); // 起始偏移
$e = (int)($_GET['e'] ?? 50); // 每批筆數
$total = 85000; // 總筆數(實際應 SELECT COUNT(*))
if ($s < $total) {
// 1. 讀取未處理批次
$sql = "SELECT * FROM `prefix_info` WHERE `isget` = 0 ORDER BY `id` DESC LIMIT $s, $e";
$res = mysqli_query($link, $sql);
while ($row = mysqli_fetch_assoc($res)) {
// 2. 寫入新表(打亂順序:這裡單純複製,實際可 ORDER BY RAND() 或應用層 shuffle)
$ins = "INSERT INTO `prefix_info_bak` (`id`, `content`, `typeid`, `isget`)
VALUES ({$row['id']}, '{$row['content']}', {$row['typeid']}, {$row['isget']})";
mysqli_query($link, $ins);
// 3. 標記原表已處理
mysqli_query($link, "UPDATE `prefix_info` SET `isget` = 1 WHERE `id` = {$row['id']}");
}
// 4. 自動跳轉下一批(Meta Refresh)
$next = $s + $e;
echo "<meta http-equiv='refresh' content='0;url=rand.php?s=$next&e=$e'>";
echo "處理中... 當前 $s 條";
} else {
echo "完成!<a href='rand.php'>再隨機一次</a>";
}
?>
<?php
// cli_rand.php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$ids = $pdo->query("SELECT id FROM prefix_info WHERE isget=0")->fetchAll(PDO::FETCH_COLUMN);
shuffle($ids);
$chunk = 500;
foreach (array_chunk($ids, $chunk) as $batch) {
$pdo->beginTransaction();
$placeholders = implode(',', array_fill(0, count($batch), '(?,?,?,?)'));
$sql = "INSERT INTO prefix_info_bak (id,content,typeid,isget) VALUES $placeholders";
$stmt = $pdo->prepare($sql);
$params = [];
foreach ($batch as $id) {
$row = $pdo->query("SELECT content,typeid,isget FROM prefix_info WHERE id=$id")->fetch();
$params[] = [$id, $row['content'], $row['typeid'], $row['isget']];
}
$stmt->execute(array_merge(...$params));
$pdo->exec("UPDATE prefix_info SET isget=1 WHERE id IN (" . implode(',', $batch) . ")");
$pdo->commit();
echo "Processed " . count($batch) . " rows\n";
}
echo "Done\n";
?>
CLI 版無超時限制、可見進度條、支援事務、效能更高。有 Shell 存取權優先用 CLI。