MySQL 數據導入恢復全流程:LOAD DATA、mysqlimport、編碼與異常處理
備份無價,恢復有價。本文詳細講解 MySQL 兩大核心導入工具 LOAD DATA INFILE 和 mysqlimport 的語法、參數、字符集處理、錯誤排查與性能優化。
方法一:LOAD DATA INFILE (SQL 語句,服務端讀取)
基本語法
LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\']
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name_or_user_var, ...)]
[SET col_name = expr, ...];
核心參數詳解
| 子句 | 默認值 | 說明 |
LOCAL | 無 | 客戶端讀取文件並發送給服務端;無需 FILE 權限,受 local_infile 參數控制 |
FIELDS TERMINATED BY | \t | 字段分隔符,CSV 用 ',' |
FIELDS ENCLOSED BY | 空 | 字段包裹字符,CSV 用 '"'" |
FIELDS ESCAPED BY | \\ | 轉義字符 |
LINES TERMINATED BY | \n | 行結束符,Windows 用 '\r\n' |
IGNORE n LINES | 0 | 跳過前 n 行 (如 CSV 表頭) |
(col_list) | 所有列 | 指定文件列映射到表列,順序可調整 |
SET col = expr | 無 | 導入時計算衍生列,如 SET created_at = NOW() |
實戰示例
-- 1. 標準制表符文件
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
-- 2. CSV 文件 (含表頭)
LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE mytbl
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
-- 3. 文件列順序與表不一致,指定映射
LOAD DATA LOCAL INFILE 'dump.txt'
INTO TABLE mytbl (b, c, a); -- 文件第1列→表b列,第2列→c,第3列→a
-- 4. 導入時轉換/賦值
LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
(order_id, product_id, qty, @price_str, @date_str)
SET price = CAST(@price_str AS DECIMAL(10,2)),
order_date = STR_TO_DATE(@date_str, '%Y-%m-%d'),
created_at = NOW();
方法二:mysqlimport (命令行工具,封裝 LOAD DATA)
# 基礎用法
mysqlimport -u root -p --local db_name dump.txt
# CSV 格式參數
mysqlimport -u root -p --local \
--fields-terminated-by=',' \
--fields-enclosed-by='"' \
--fields-escaped-by='"' \
--lines-terminated-by='\r\n' \
--ignore-lines=1 \
db_name data.csv
# 指定列映射
mysqlimport -u root -p --local \
--columns=b,c,a \
db_name dump.txt
mysqlimport 文件命名規約
- 文件名必須與表名一致 (不含擴展名)
- 如表
users,文件需命名為 users.txt、users.csv 等
- 多文件並行導入:
mysqlimport ... db_name file1.txt file2.txt
字符集與編碼處理
💡 核心原則:文件編碼 = CHARACTER SET 參數 = 表/列字符集
三者不一致會導致亂碼或截斷。
常見編碼場景
| 文件編碼 | LOAD DATA 參數 | 表字符集 | 備註 |
| UTF-8 | CHARACTER SET utf8mb4 | utf8mb4 | 推薦標準 |
| GBK/GB2312 | CHARACTER SET gbk | utf8mb4 | MySQL 自動轉換 |
| Latin1 | CHARACTER SET latin1 | utf8mb4 | 舊系統遷移 |
檢測文件編碼
# Linux
file data.csv
# 輸出: UTF-8 Unicode text, with CRLF line terminators
# 或
enca data.csv
chardet data.csv
錯誤處理與數據質量
錯誤行處理模式
-- 忽略錯誤行 (需配合 --local)
LOAD DATA LOCAL INFILE 'bad_data.txt' IGNORE INTO TABLE t;
-- 替換模式 (主鍵/唯一鍵衝突時替換)
LOAD DATA LOCAL INFILE 'data.txt' REPLACE INTO TABLE t;
導入後驗證
-- 行數核對
SELECT COUNT(*) FROM tbl_name;
-- 抽樣檢查
SELECT * FROM tbl_name ORDER BY RAND() LIMIT 10;
-- 關鍵字段非空/唯一檢查
SELECT COUNT(*) FROM tbl_name WHERE key_col IS NULL;
SELECT key_col, COUNT(*) FROM tbl_name GROUP BY key_col HAVING COUNT(*) > 1;
性能優化建議
- 禁用索引後重建 (大表導入):
ALTER TABLE t DROP INDEX idx1, DROP INDEX idx2;
LOAD DATA ... INTO TABLE t;
ALTER TABLE t ADD INDEX idx1 (...), ADD INDEX idx2 (...);
- 調大緩衝區:
SET GLOBAL bulk_insert_buffer_size = 256M; -- MyISAM
SET GLOBAL innodb_buffer_pool_size = 4G; -- InnoDB
- 關閉外鍵檢查/唯一檢查 (確保數據乾淨時):
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
LOAD DATA ...;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
- 分區表並行導入:按分區拆分文件,多線程並行
常見錯誤排查
| 錯誤 | 原因 | 解決 |
File not found | 路徑錯誤/權限不足 | 用絕對路徑;確保 mysql 進程用戶可讀 |
local_infile disabled | 服務端/客戶端未啟用 LOCAL | SET GLOBAL local_infile=1;;客戶端 --local-infile=1 |
Data too long | 字段長度不足 | 擴大列定義或截斷數據 |
Incorrect integer value | 非數值導入數值列 | 清洗數據或用 SET 轉換 |
Duplicate entry | 唯一鍵衝突 | 用 REPLACE/IGNORE 或預清洗 |