核心語法:跨表數據複製
INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table
[WHERE condition];
基礎用法:全量複製表結構與數據
-- 僅複製數據(目標表已存在)
INSERT INTO user1 (name, address, phone)
SELECT name, address, phone FROM user1;
進階場景:避免重複數據插入
方案一:NOT IN 子查詢去重
INSERT INTO t_test (Name, Value)
SELECT 'testName', 'testValue' FROM DUAL
WHERE 'testName' NOT IN (SELECT Name FROM t_test);
注意:若子查詢返回 NULL,NOT IN 失效。建議字段加 NOT NULL 或改用 NOT EXISTS。
方案二:NOT EXISTS 關聯去重(推薦)
INSERT INTO t_test (Name, Value)
SELECT 'testName', 'testValue' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM t_test WHERE Name = 'testName');
方案三:INSERT IGNORE + 唯一索引(最高效)
-- 先確保目標字段有唯一索引
ALTER TABLE t_test ADD UNIQUE KEY uk_name (Name);
-- 直接插入,重複自動忽略
INSERT IGNORE INTO t_test (Name, Value) VALUES ('testName', 'testValue');
方案四:ON DUPLICATE KEY UPDATE(存在則更新)
INSERT INTO t_test (Name, Value) VALUES ('testName', 'testValue')
ON DUPLICATE KEY UPDATE Value = VALUES(Value);
多表數據合併插入
INSERT INTO clients (client_id, client_name, client_type)
SELECT supplier_id, supplier_name, supplier_type
FROM suppliers
WHERE NOT EXISTS (
SELECT 1 FROM clients WHERE clients.client_id = suppliers.supplier_id
);
性能優化要點
- 目標表字段加唯一索引:支撐 IGNORE / ON DUPLICATE KEY 高效去重
- 批量提交:大量數據分批
LIMIT 10000 循環插入
- 關閉非必要索引:導入前
DROP INDEX,導入後重建
- 禁用外鍵檢查:
SET FOREIGN_KEY_CHECKS = 0; 導入後恢復
- 使用事務:
START TRANSACTION; ... COMMIT; 保證原子性
總結選型建議
| 場景 | 推薦方案 | 核心優勢 |
| 目標表有唯一鍵,重複即跳過 | INSERT IGNORE | 語法簡潔、性能最優 |
| 重複時需更新其他字段 | ON DUPLICATE KEY UPDATE | 單語句完成增/改 |
| 無唯一鍵,邏輯去重 | NOT EXISTS 子查詢 | NULL安全、語義清晰 |
| 全量初始化大表 | LOAD DATA INFILE | 比 INSERT 快 10-20 倍 |