for ($i=0; $i<=5; $i++) {
$sql = "INSERT INTO users (uid, name) VALUES ($i, 'soulcy')";
mysql_query($sql); // 每次網路往返、解析、鎖、提交
}
5 行尚可,500 萬行會拖垮資料庫。
INSERT INTO `users` (`uid`, `name`) VALUES
(0, 'soulcy'), (1, 'soulcy'), (2, 'soulcy'), (3, 'soulcy'), (4, 'soulcy'), (5, 'soulcy');
$name = 'soulcy';
$batch = [];
for ($i = 0; $i <= 5000; $i++) {
$batch[] = "($i, '$name')";
if (count($batch) === 1000) { // 分批 1000 行
$sql = "INSERT INTO users (uid, name) VALUES " . implode(',', $batch);
mysqli_query($link, $sql);
$batch = [];
}
}
if ($batch) {
$sql = "INSERT INTO users (uid, name) VALUES " . implode(',', $batch);
mysqli_query($link, $sql);
}
-- 1. 產生 CSV/TSV 檔案(應用層寫檔)
-- 2. 伺服端導入
LOAD DATA INFILE '/tmp/users.txt'
INTO TABLE users
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(uid, name);
secure_file_priv 允許目錄、FILE 權限。ALTER TABLE t DISABLE KEYS(MyISAM)/ 手動 DROP INDEX 後重建(InnoDB)。SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;COMMIT),避免超大事務鎖住資源、Undo Log 爆漲。Innodb_buffer_pool_wait_free、Innodb_log_waits。