一、INSERT 語句多樣式
-- 1. 全列插入（須按建表順序）
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
-- 2. 指定列插入（推薦，結構變更不影響）
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
-- 3. SET 語法（列名值對應清晰）
INSERT INTO users SET name='Carol', email='carol@example.com';
-- 4. 子查詢插入（跨表複製）
INSERT INTO users_archive (name, email)
SELECT name, email FROM users WHERE created_at < '2023-01-01';
-- 5. 批量插入（單條 SQL，性能最佳）
INSERT INTO users (name, email) VALUES
('Dave','dave@ex.com'),('Eve','eve@ex.com'),('Frank','frank@ex.com');
二、LOAD DATA INFILE 極速導入（服務端讀文件）
LOAD DATA [LOCAL] INFILE 'data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(name, email, @created_str)
SET created_at = STR_TO_DATE(@created_str, '%Y-%m-%d %H:%i:%s');
參數詳解
| 子句 | 作用 | 默認值 |
|---|
| FIELDS TERMINATED BY | 列分隔符 | \t (Tab) |
| FIELDS ENCLOSED BY | 列包裹字符 | 無 |
| FIELDS ESCAPED BY | 轉義字符 | \\ |
| LINES TERMINATED BY | 行結束符 | \n |
| IGNORE n LINES | 跳過前 n 行（如標題行） | 0 |
| SET col=expr | 導入時轉換/賦值 | 無 |
LOCAL 關鍵字差異
- 無 LOCAL：文件在服務器主機，需
FILE 權限，路徑相對 datadir
- 有 LOCAL：文件在客戶端主機，由客戶端讀取傳給服務器，無需
FILE 權限，路徑相對客戶端當前目錄
三、mysqlimport 命令行工具（LOAD DATA 封裝）
# 基本用法：文件名(去擴展名) = 表名
mysqlimport -u root -p --local \
--fields-terminated-by=',' \
--fields-enclosed-by='"' \
--lines-terminated-by='\r\n' \
--ignore-lines=1 \
mydb users.csv
# 多文件導入同一表
mysqlimport ... mydb users_1.csv users_2.csv
# 文件名 users_1.csv → 表 users
四、大數據量導入優化清單
- 關閉索引：
ALTER TABLE t DISABLE KEYS; 導入後 ENABLE KEYS（MyISAM 有效，InnoDB 無效）
- 關閉外鍵檢查：
SET FOREIGN_KEY_CHECKS=0; 導入後 =1
- 關閉唯一性檢查：
SET UNIQUE_CHECKS=0;
- 自動提交關閉：
SET AUTOCOMMIT=0; 批量提交
- 使用
LOAD DATA 替換 INSERT，速度提升 10-20 倍
- 分區表按分區並行導入
五、常見報錯排查
| 錯誤 | 原因 | 解決 |
|---|
| File not found | 路徑錯誤/權限不足 | 絕對路徑、chmod 644、檢查 secure_file_priv |
| Incorrect integer value | 數據類型不匹配 | SET 子句轉換、清洗源文件 |
| Duplicate entry | 主鍵/唯一鍵衝突 | REPLACE INTO / IGNORE / ON DUPLICATE KEY UPDATE |