MySQL INSERT/REPLACE/LOAD DATA 資料寫入全方位指南：語法、效能、批次策略與陷阱

INSERT 語法完整形態

-- 1. 完整欄位值（須按建表順序）
INSERT INTO tbl VALUES (val1, val2, ...);

-- 2. 指定欄位（推薦，防結構變更失效）
INSERT INTO tbl (col1, col2) VALUES (val1, val2);

-- 3. 多行批次（單條 SQL，效能最佳）
INSERT INTO tbl (col1, col2) VALUES 
    (v11, v12), (v21, v22), ... (vN1, vN2);

-- 4. SET 語法（可讀性佳，僅單行）
INSERT INTO tbl SET col1=val1, col2=val2;

-- 5. 運算式引用前欄位
INSERT INTO tbl (a, b) VALUES (10, a*2);  -- 合法
INSERT INTO tbl (a, b) VALUES (b*2, 10);  -- 非法

REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE

結論：生產環境優先用 ON DUPLICATE KEY UPDATE，REPLACE 僅用於確需重建行的場景。

INSERT ... SELECT 跨表複製

-- 完整複製
INSERT INTO target_tbl SELECT * FROM source_tbl;

-- 指定欄位 + 條件
INSERT INTO archive_log (id, msg, created)
SELECT id, msg, created FROM log WHERE created < '2023-01-01';

-- 禁止：目標表出現在 FROM 子句（ANSI 標準禁止）
-- INSERT INTO t SELECT * FROM t;  -- 錯誤

LOAD DATA INFILE 最高效批次匯入

LOAD DATA [LOCAL] INFILE 'file.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl
    [CHARACTER SET utf8mb4]
    [FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\']
    [LINES TERMINATED BY '\n']
    [IGNORE 1 LINES]  -- 跳過標題行
    [(col1, col2, ...)];  -- 指定欄位順序

關鍵參數對照

mysqlimport 命令列封裝

# 等同 LOAD DATA，檔名決定表名
mysqlimport -u root -p --local --fields-terminated-by=, --fields-enclosed-by='"' db_name file.txt

效能優化黃金法則

1. 批次大小：INSERT 批次 1,000~5,000 行，LOAD DATA 無上限
2. 關閉檢查：SET unique_checks=0; foreign_key_checks=0; 匯入後開啟
3. 關閉索引：MyISAM ALTER TABLE DISABLE KEYS，InnoDB 無效
4. 事務包裹：InnoDB 批次 INSERT 單事務提交
5. 並行匯入：分區表/分表平行 LOAD DATA

常見陷阱避坑

• CSV 匯入：FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
• Windows 換行：LINES TERMINATED BY '\r\n'
• NULL 值：檔案用 \N 表示，非空字串 'NULL'
• 編碼不匹配：CHARACTER SET utf8mb4 強制指定
• 權限不足：需 FILE 權限（非 LOCAL 時）

1. 上一篇文章： MySQL 主從複制配置完整指南：GTID 模式、雙主架構、故障切換與監控
2. 下一篇文章： MySQL UPDATE/DELETE 資料修改刪除實戰：語法、條件構建、效能優化與安全防護

MySQL 資料庫相關知識

SQL Server COALESCE 函數進階實戰：動態 SQL、Pivot 轉置、批次 Kill Session

COALESCE vs ISNULL 核心差異 特性COA

SQL 語句效能調校深度指南：索引設計、查詢重寫、子查詢優化與實戰案例

一、資料庫設計與規劃 主鍵長度極小化：能用 SMALLIN

Linux 系統下 MySQL 安裝、備份與 Root 密碼恢復完整指南

MySQL 安裝前的準備與檢測 在 RedHat 9 系統中

Struts 2 標籤庫實戰：append 標籤合併集合與 generator 標籤生成迭代器

Struts 2 提供豐富的標籤庫簡化視圖層開發。本文聚焦兩