導出：SELECT INTO OUTFILE (服務端文件)
SELECT
employee.id, employee.first_name, employee.last_name,
employee.start_date, employee.salary, employee.city
FROM employee
INTO OUTFILE 'C:/employee.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
- 需
FILE 權限，受 secure_file_priv 限制。
- 默認 Tab 分隔、無引號、
\n 換行。
- 可自定義
FIELDS TERMINATED BY、ENCLOSED BY、LINES TERMINATED BY。
導入：LOAD DATA INFILE (服務端文件)
LOAD DATA INFILE 'C:/employee.txt'
INTO TABLE employee
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
(id, first_name, last_name, start_date, salary, city);
- 字段列表指定映射順序，CSV 首行無表頭時必須指定。
- 缺失字段按默認值/NULL 填充。
本地文件導入：LOAD DATA LOCAL INFILE (客戶端文件)
mysql> LOAD DATA LOCAL INFILE 'C:/mytable.txt' INTO TABLE mytable
LINES TERMINATED BY '\r\n';
- 文件在**客戶端**機器，通過協議傳輸到服務端。
- 需客戶端與服務端均啟用
local_infile=1。
- 適合應用服務器直接導入本地 CSV，無需上傳到數據庫服務器。
命令行導出/導入管道
# 導出
mysql -h host -u user -p -e "SELECT * FROM db.tbl" > data.txt
# 導入 (配合 LOAD DATA LOCAL INFILE)
mysql -h host -u user -p --local-infile=1 -e "LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE db.tbl"
字符集與編碼注意
- 導出文件編碼 = 連接字符集 (
SET NAMES / --default-character-set)。
- 導入時指定
CHARACTER SET utf8mb4 確保正確解碼。
- Excel 兼容：導出
ENCLOSED BY '"' TERMINATED BY ',' LINES TERMINATED BY '\r\n'，另存為 CSV UTF-8 (BOM) 最穩。