MySQL 百萬級測試資料快速生成：PHP 落檔 + LOAD DATA 實測 1 分鐘/百萬

對比實測

方案一：PHP 生成 TSV 檔案

<?php
$t = microtime(true);
set_time_limit(0);
$f = fopen('e:/insert.sql', 'wb');
$row = "268\t2\t'0,262,268,'\t0\t'2342'\t'423423'\t'123123'\t'2012-01-09 09:55:43'\t'upload/a.jpg'\t'upload/thumb_a.jpg'\tNULL\tNULL\t38\t'件'\t''\t123\t123\t0";
for ($i=0; $i<1000000; $i++) {
    fwrite($f, $row . "\r\n");
}
fclose($f);
echo "寫入完成，耗時: " . (microtime(true)-$t) . " 秒";

• 欄位用 \t 分隔，行用 \r\n 結尾。
• NULL 欄位直接寫 NULL (不加引號)。

方案二：LOAD DATA 導入

LOAD DATA LOCAL INFILE 'e:/insert.sql'
INTO TABLE tenmillion
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n'
(categ_id, categ_fid, SortPath, address, p_identifier, pro_specification, name, description, add_date, picture_url, thumb_url, shop_url, shop_thumb_url, brand_id, unit, square_meters_unit, market_price, true_price, square_meters_price);

• LOCAL 表示檔案在 Client 端，由 Client 讀取傳給 Server。
• 欄位須與表結構嚴格對應。

方案三：純 SQL 自增式複製 (無需外部檔案)

-- 假設 itemid 為自增主鍵
INSERT INTO downitems (chid, catid, softid, ...)
SELECT chid, catid, softid, ...
FROM downitems
WHERE itemid >= (
    SELECT FLOOR(RAND() * (SELECT MAX(itemid) FROM downitems))
)
ORDER BY itemid LIMIT 1000;

• 首次從 1 萬行隨機抽 1000 行插入 → 2 萬行。
• 再執行幾次，指數級增長：1000 → 5000 → 1 萬 → 5 萬 → 100 萬。
• 利用 RAND() * MAX(id) 隨機取樣，避免連續塊鎖爭用。

關鍵效能參數 (my.cnf)

[mysqld]
innodb_buffer_pool_size = 2G      # 導入時可暫時調大
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
bulk_insert_buffer_size = 256M    # MyISAM 專用
key_buffer_size = 512M            # MyISAM 專用

結語

**PHP/Shell 落地 TSV/CSV + LOAD DATA** 是生成百萬測試資料的標準最優解：無 SQL 解析開銷、順序寫入、可斷點續傳。純 SQL 方案適合無法落地檔案的受限環境，利用隨機抽樣指數增長同樣高效。