萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 數據導入恢復完整指南:LOAD DATA、mysqlimport、字符集處理

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 LINES0跳過前 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-8CHARACTER SET utf8mb4utf8mb4推薦標準
GBK/GB2312CHARACTER SET gbkutf8mb4MySQL 自動轉換
Latin1CHARACTER SET latin1utf8mb4舊系統遷移

檢測文件編碼

# 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;

性能優化建議

  1. 禁用索引後重建 (大表導入):
    ALTER TABLE t DROP INDEX idx1, DROP INDEX idx2;
    LOAD DATA ... INTO TABLE t;
    ALTER TABLE t ADD INDEX idx1 (...), ADD INDEX idx2 (...);
  2. 調大緩衝區:
    SET GLOBAL bulk_insert_buffer_size = 256M;  -- MyISAM
    SET GLOBAL innodb_buffer_pool_size = 4G;    -- InnoDB
  3. 關閉外鍵檢查/唯一檢查 (確保數據乾淨時):
    SET FOREIGN_KEY_CHECKS = 0;
    SET UNIQUE_CHECKS = 0;
    LOAD DATA ...;
    SET FOREIGN_KEY_CHECKS = 1;
    SET UNIQUE_CHECKS = 1;
  4. 分區表並行導入:按分區拆分文件,多線程並行

常見錯誤排查

錯誤原因解決
File not found路徑錯誤/權限不足用絕對路徑;確保 mysql 進程用戶可讀
local_infile disabled服務端/客戶端未啟用 LOCALSET GLOBAL local_infile=1;;客戶端 --local-infile=1
Data too long字段長度不足擴大列定義或截斷數據
Incorrect integer value非數值導入數值列清洗數據或用 SET 轉換
Duplicate entry唯一鍵衝突用 REPLACE/IGNORE 或預清洗
copyright © 萬盛學電腦網 all rights reserved