MySQL 批次 INSERT 多筆資料：單條語句高效寫入實戰

語法結構

INSERT INTO table (col1, col2, ...)
VALUES
  (v11, v12, ...),
  (v21, v22, ...),
  ...
  (vn1, vn2, ...);

實戰範例

CREATE TABLE authors (
  auth_id SMALLINT PRIMARY KEY,
  auth_fn VARCHAR(20),
  auth_mn VARCHAR(20),
  auth_ln VARCHAR(20)
) ENGINE=InnoDB;

INSERT INTO authors VALUES
(1006, 'H', 'S.', 'T'),
(1007, 'J', 'C', 'O'),
(1008, 'B', NULL, 'E'),
(1009, 'R', 'M', 'R'),
(1010, 'J', 'K', 'T'),
(1011, 'J', 'G.', 'N'),
(1012, 'A', NULL, 'P'),
(1013, 'A', NULL, 'W'),
(1014, 'N', NULL, 'A');

效能優勢

最佳實踐

1. 批次大小：500~2000 行/批，單條 SQL < 4MB（max_allowed_packet）
2. 事務包裹：START TRANSACTION; ...批次 INSERT... COMMIT; 進一步減少提交開銷
3. 主鍵順序：按主鍵遞增插入，減少頁面分裂
4. LOAD DATA：超大量級（百萬行+）優先考慮 LOAD DATA INFILE，速度更快
5. ON DUPLICATE KEY UPDATE：同步寫入場景合併 INSERT/UPDATE

應用層範例

# Python 批次組裝
rows = [(i, f'fn{i}', f'mn{i}', f'ln{i}') for i in range(10000)]
chunks = [rows[i:i+1000] for i in range(0, len(rows), 1000)]
for chunk in chunks:
    placeholders = ', '.join(['(%s,%s,%s,%s)'] * len(chunk))
    sql = f'INSERT INTO authors VALUES {placeholders}'
    cursor.execute(sql, [v for row in chunk for v in row])
conn.commit()

批次 INSERT 是提升寫入吞吐最低成本、最高收益的優化手法，請養成習慣。