基本語法

-- 1. 全列插入（須與表結構順序一致，不推薦生產用）
INSERT INTO `table_name` VALUES (val1, val2, ...);

-- 2. 指定列插入（推薦：順序無關、擴展兼容、可讀）
INSERT INTO `table_name` (col1, col2, col3) 
VALUES (val1, val2, val3);

-- 3. 批量插入（單語句多行，性能最優）
INSERT INTO `table_name` (col1, col2) 
VALUES 
    (v11, v12),
    (v21, v22),
    (v31, v32);

-- 4. 查詢結果插入（INSERT ... SELECT）
INSERT INTO `archive_orders` (id, user_id, amount, created_at)
SELECT id, user_id, amount, created_at FROM `orders` WHERE `status` = 'completed';

核心參數與行為

批量插入性能實測（10 萬行）

子句/選項	作用	示例
IGNORE	重複鍵/約束違規時跳過，不報錯	INSERT IGNORE INTO ...
ON DUPLICATE KEY UPDATE	衝突時更新（Upsert）	見專文 178059
REPLACE INTO	衝突時 DELETE+INSERT（自增 ID 變化）	慎用
DELAYED	緩衝插入（MyISAM/MEMORY，8.0 移除）	異步寫入
HIGH_PRIORITY / LOW_PRIORITY	調整語句優先級	MyISAM 表鎖場景

方式	耗時	QPS	備註
單條 INSERT 循環（自動提交）	45 s	2,200	每行一次提交、網絡往返
事務包裹單條 INSERT	8 s	12,500	手動 COMMIT 批次
多行 VALUES 批量（1000 行/批）	1.2 s	83,000	單語句、解析一次、索引批量維護
LOAD DATA INFILE	0.4 s	250,000	直接讀文件、跳過 SQL 層、最快

LOAD DATA 實戰模板

LOAD DATA INFILE '/data/import/users.csv'
INTO TABLE `users`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES  -- 跳過表頭
(`email`, `name`, `phone`, @created_at)
SET `created_at` = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');

獲取自增 ID

-- 1. LAST_INSERT_ID() / mysql_insert_id()：返回第一行自增值
INSERT INTO `users` (`name`) VALUES ('A'), ('B'), ('C');
SELECT LAST_INSERT_ID();  -- 返回 A 的 ID（假設 100）
-- B=101, C=102 連續分配

-- 2. 批量插入後獲取所有 ID（需連續且無併發）
SET @first_id = LAST_INSERT_ID();
SELECT @first_id + n - 1 AS id FROM numbers WHERE n <= 3;  -- 生成 100,101,102

-- 3. RETURNING (MySQL 8.0.27+ / MariaDB 10.5+)
INSERT INTO `users` (`name`) VALUES ('D') RETURNING `id`, `created_at`;

避坑清單

坑點	後果	規避
未指定列，表結構變更報錯	Column count doesn't match	永遠顯式列出目標列
批量 VALUES 過大	max_allowed_packet 錯誤 / 內存 OOM	分批 1000-5000 行，調大參數
字符集不匹配導致截斷/亂碼	Data truncated / 亂碼	連接/表/文件統一 utf8mb4
自增 ID 耗盡	Duplicate entry '0' for key PRIMARY	用 BIGINT UNSIGNED（1.8e19）、監控剩餘值
外鍵檢查失敗	Cannot add or update child row	先插父表、或暫時 SET FOREIGN_KEY_CHECKS=0

生產環境最佳實踐

1. 應用層構建批量 INSERT：累積 500-2000 行 → 執行 → 提交
2. 使用參數化查詢防注入：INSERT ... VALUES (?,?), (?,?)
3. 大數據導入：LOAD DATA + 禁索引/外鍵/唯一檢查 → 導入 → 重建
4. 幂等寫入：唯一索引 + ON DUPLICATE KEY UPDATE / INSERT IGNORE
5. 審計欄位自動填充：DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP / 觸發器