MySQL 高效導入導出：SELECT INTO OUTFILE 與 LOAD DATA INFILE 實戰

MySQL 高效導入導出：SELECT INTO OUTFILE 與 LOAD DATA INFILE 實戰

對比 mysqldump 邏輯備份，SELECT ... INTO OUTFILE + LOAD DATA INFILE 以文本流方式直接讀寫存儲引擎，速度提升 5-10 倍，適合大批量數據遷移、ETL 落地、分區表數據搬遷。

一、導出數據：SELECT INTO OUTFILE

語法

SELECT [字段列表] 
FROM 表名 
INTO OUTFILE '文件路徑'
[CHARACTER SET charset]
[FIELDS TERMINATED BY '\t' 
  [[OPTIONALLY] ENCLOSED BY '"'] 
  ESCAPED BY '\\']
[LINES TERMINATED BY '\n'];

常用示例

-- 1. 全表導出，默認分隔符 \t，換行 \n
SELECT * FROM phpernote INTO OUTFILE '/tmp/phpernote.txt';

-- 2. 指定字段、CSV 格式 (Excel 友好)
SELECT id, name, email, created_at 
FROM users 
INTO OUTFILE '/tmp/users.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

-- 3. 條件導出
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
INTO OUTFILE '/tmp/orders_2024.txt';

關鍵限制與權限

• 需 FILE 權限 (極高危，僅 DBA/ETL 賬號持有)。
• 文件路徑必須在 secure_file_priv 目錄下 (MySQL 5.6+ 默認限制)，SHOW VARIABLES LIKE 'secure_file_priv'; 查看。
• 目標文件不能存在，否則報錯 File already exists。
• 導出文件由 MySQL 服務進程用戶創建，屬主為 mysql，應用層需注意讀取權限。

二、導入數據：LOAD DATA INFILE

語法

LOAD DATA [LOCAL] INFILE '文件路徑'
[REPLACE | IGNORE]
INTO TABLE 表名
[CHARACTER SET charset]
[FIELDS TERMINATED BY '\t' 
  [[OPTIONALLY] ENCLOSED BY '"'] 
  ESCAPED BY '\\']
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(字段列表)]
[SET col_name = expr, ...];

常用示例

-- 1. 全字段導入 (文件列順序 = 表結構順序)
LOAD DATA INFILE '/tmp/phpernote.txt' INTO TABLE phpernote;

-- 2. 指定字段映射 (文件列順序 ≠ 表結構)
LOAD DATA INFILE '/tmp/users.csv' 
INTO TABLE users 
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES  -- 跳過 CSV 標題行
(id, name, email, @created_str)
SET created_at = STR_TO_DATE(@created_str, '%Y-%m-%d %H:%i:%s');

-- 3. 本地文件導入 (客戶端讀取文件發送給服務端，無需 FILE 權限)
LOAD DATA LOCAL INFILE 'C:/data/users.csv' INTO TABLE users ...;

關鍵參數解析

三、性能優化最佳實踐

1. 禁用索引/外鍵/唯一檢查 (導入前)：

   
     SET FOREIGN_KEY_CHECKS=0;
     SET UNIQUE_CHECKS=0;
     ALTER TABLE tbl DISABLE KEYS; -- 僅 MyISAM 有效，InnoDB 無此語法
     

2. 調大緩沖區：

   
     SET GLOBAL bulk_insert_buffer_size = 256*1024*1024; -- 256MB
     

3. 分區表並行導入：按分區拆分文件，多線程並發 LOAD DATA。
4. 事務控制：InnoDB 單次 LOAD DATA 即一個事務，超大文件建議拆分 split -l 100000 避免長事務膨脹 Undo Log。
5. 字符集一致：文件編碼、CHARACTER SET 參數、表字符集三者統一，防止亂碼。

四、對比總結

掌握 SELECT INTO OUTFILE / LOAD DATA INFILE 參數微調，是 MySQL 大數據量搬遷的核心技能。