MySQL 資料匯入全攻略：INSERT、LOAD DATA、mysqlimport 三大方式深度解析

資料寫入是資料庫運維的基礎操作。MySQL 提供多種匯入路徑：標準 INSERT 適合少量業務寫入，LOAD DATA INFILE 針對大批量文字檔案極速載入，mysqlimport 則是命令列自動化的最佳夥伴。本文系統整理三大方式語法、參數、適用場景與避坑指南。

方式一：INSERT 語句 —— 靈活通用的標準寫入

四種基本語法形式

-- 1. 全欄位按序插入（須匹配表結構順序）
INSERT INTO tablename VALUES (value1, value2, ...);

-- 2. 指定欄位插入（推薦，順序可調整、擴充欄位不影響）
INSERT INTO tablename (field1, field2, ...) VALUES (value1, value2, ...);

-- 3. SET 語法（類似 UPDATE，欄位值對應直觀）
INSERT INTO tablename SET field1=value1, field2=value2, ...;

-- 4. 從另一張表查詢插入（跨表複製/ETL 常用）
INSERT INTO tablename (field1, field2) SELECT field1, field2 FROM sourcetable;

批次多行插入：效能提升關鍵

INSERT INTO tablename VALUES
    (val1, val2, ...),
    (val1, val2, ...),
    (val1, val2, ...);

優勢：單條 SQL 完成多行寫入，減少網路往返、解析優化開銷、WAL 刷盤次數。建議單批次 500-2000 行，配合 max_allowed_packet 調整。

INSERT 變體與鎖控制

方式二：LOAD DATA INFILE —— 百萬級秒級匯入的利器

核心語法結構

LOAD DATA [LOCAL] INFILE 'filename'
    INTO TABLE tablename
    [CHARACTER SET charset_name]
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var, ...)]
    [SET col_name = expr, ...]

關鍵參數詳解

檔案路徑解析規則

• 無 LOCAL：Server 端讀檔。絕對路徑從根目錄；相對路徑從 datadir 起算。需 FILE 權限，檔案必須 Server 可讀
• 有 LOCAL：Client 端讀檔傳輸。絕對路徑從客戶機根目錄；相對路徑從客戶端當前工作目錄。無需 FILE 權限

實戰範例：匯入 CSV 檔案

LOAD DATA LOCAL INFILE 'sample.txt'
    INTO TABLE sample
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (id, name, email, created_at);

方式三：mysqlimport —— 命令列自動化批量匯入

mysqlimport 本質是 LOAD DATA INFILE 的命令列封裝，支援萬用字元批次處理多檔案。

基本語法

mysqlimport [options] db_name textfile1 [textfile2 ...]

常用選項對照表

檔案命名與表對應規則

• 檔案名（去除副檔名）即為目標表名：user.txt → 表 user
• 多檔案同表：user.1.txt、user.2.txt 會依序匯入同一張表
• 執行前必須確認目標表已存在

實戰範例

# 匯入單一檔案（CSV 格式，含標題行）
mysqlimport -u root -p --local \
    --fields-terminated-by=',' \
    --fields-enclosed-by='"' \
    --lines-terminated-by='\r\n' \
    --ignore-lines=1 \
    mydb user.csv

# 批次匯入多張表
mysqlimport -u root -p --local \
    --fields-terminated-by=',' \
    mydb user.csv order.csv product.csv

三大方式效能與選型對比

最佳實踐建議

1. 大批量首選 LOAD DATA：配合 --local 避免權限問題，預先 SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 加速，匯入後恢復
2. 字元集一致性：檔案編碼、CHARACTER SET 子句、表欄位字元集三者必須一致，否則亂碼
3. 日期時間格式：檔案中日期格式需符合 MySQL 標準 (YYYY-MM-DD HH:MM:SS)，或用 SET col = STR_TO_DATE(@var, '%format%') 轉換
4. 主鍵/唯一鍵衝突策略：REPLACE 或 IGNORE 關鍵字控制行為
5. 自動化管線：mysqlimport + Shell/Python 腳本 + crontab 實現定時 ETL

總結

MySQL 資料匯入無最好，只有最適合：日常業務寫入用 INSERT（含 ON DUPLICATE KEY UPDATE）；大批量離線導入用 LOAD DATA INFILE；定時自動化腳本用 mysqlimport。掌握三者語法細節與效能特性，讓資料流轉更高效穩定。