萬盛學電腦網

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

MySQL INSERT INTO SELECT 表數據復制與去重實戰

核心語法:跨表數據複製

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

性能優化要點

  1. 目標表字段加唯一索引:支撐 IGNORE / ON DUPLICATE KEY 高效去重
  2. 批量提交:大量數據分批 LIMIT 10000 循環插入
  3. 關閉非必要索引:導入前 DROP INDEX,導入後重建
  4. 禁用外鍵檢查:SET FOREIGN_KEY_CHECKS = 0; 導入後恢復
  5. 使用事務:START TRANSACTION; ... COMMIT; 保證原子性

總結選型建議

場景推薦方案核心優勢
目標表有唯一鍵,重複即跳過INSERT IGNORE語法簡潔、性能最優
重複時需更新其他字段ON DUPLICATE KEY UPDATE單語句完成增/改
無唯一鍵,邏輯去重NOT EXISTS 子查詢NULL安全、語義清晰
全量初始化大表LOAD DATA INFILE比 INSERT 快 10-20 倍
copyright © 萬盛學電腦網 all rights reserved