引用

作為一個(gè)軟件開發(fā)人員，特別是要與數(shù)據(jù)庫打交道的靚仔，你就不可避免的要遇到數(shù)據(jù)重復(fù)插入的問題，如果你沒有唯一鍵或者主鍵之類的，那無話可說，但是你很好的設(shè)計(jì)了數(shù)據(jù)庫唯一索引和主鍵，哪當(dāng)我們插入數(shù)據(jù)的時(shí)候，你就必須做好去重處理。

代碼里面有很多方法，預(yù)檢查，分布式鎖等等；但是有一些場景，我們要快速遷移大量的數(shù)據(jù)，有的時(shí)候會(huì)分批次多階段的處理，這個(gè)時(shí)候如果都來做預(yù)檢就很耗時(shí)，本文將介紹INSERT IGNORE的用法，特別適合解決這一問題。

一、 基本語法

INSERT IGNORE 是 MySQL 中一個(gè)非常實(shí)用的語句，用于在插入數(shù)據(jù)時(shí)避免重復(fù)鍵錯(cuò)誤（如主鍵沖突或唯一索引沖突）。它可以幫助你優(yōu)雅地處理數(shù)據(jù)重復(fù)問題，而不會(huì)拋出錯(cuò)誤。

INSERT IGNORE 的基本語法與普通的 INSERT 語句非常相似，只是在 INSERT 關(guān)鍵字后面加上了 IGNORE 關(guān)鍵字。以下是常見的語法形式：

• 插入單行數(shù)據(jù)

INSERT IGNORE INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

• 插入多行數(shù)據(jù)

INSERT IGNORE INTO table_name (column1, column2, ..., columnN)
VALUES 
    (value1a, value2a, ..., valueNa),
    (value1b, value2b, ..., valueNb),
    ...
    (value1n, value2n, ..., valueNn);

• 插入查詢結(jié)果

INSERT IGNORE INTO table_name (column1, column2, ..., columnN)
SELECT column1, column2, ..., columnN
FROM another_table
WHERE condition;

二、 工作原理

INSERT IGNORE 的主要功能是忽略插入操作中可能導(dǎo)致重復(fù)鍵錯(cuò)誤的行。以下是其工作原理的詳細(xì)說明：

2.1 忽略重復(fù)鍵錯(cuò)誤

當(dāng)嘗試插入的數(shù)據(jù)違反了表的唯一性約束（如主鍵或唯一索引）時(shí)，INSERT IGNORE 會(huì)自動(dòng)忽略該插入操作，不會(huì)拋出錯(cuò)誤。例如：

假設(shè)有一個(gè)表 users，結(jié)構(gòu)如下：

CREATE TABLE `user_info` (
  `id` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用戶id',
  `risk_score` int NOT NULL DEFAULT '0' COMMENT '風(fēng)險(xiǎn)評(píng)分 0-100 （不可超出該范圍）',
  `risk_level` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0-安全 1-低風(fēng)險(xiǎn) 2-中風(fēng)險(xiǎn) 3-高風(fēng)險(xiǎn)',
  `register_time` datetime NOT NULL COMMENT '注冊時(shí)間',
  `created` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間(北京時(shí)間)',
  `updated` datetime DEFAULT NULL COMMENT '修改時(shí)間(北京時(shí)間)',
  `creator` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '創(chuàng)建人',
  `updater` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (`id`),
  KEY `idx_updated` (`updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用戶安全信息表';

如果嘗試插入以下SQL:

INSERT IGNORE INTO `user_info`
(`id`, `risk_score`, `risk_level`, `register_time`, `created`, `updated`, `creator`, `updater`) 
VALUES 
('11-000034639cc4408aba88358e73d48517', 10, 0, '2023-02-28 20:28:58', '2025-03-21 17:02:21', '2025-03-21 17:02:36', 'admin', 'admin');

通過下圖可以看到，當(dāng)11-000034639cc4408aba88358e73d48517已經(jīng)存在的時(shí)候，MySQL 會(huì)忽略該插入操作，不會(huì)報(bào)錯(cuò)。

注意：這里只是根據(jù)唯一鍵和主鍵去判斷是否存在，至于其它的值是否發(fā)生變化不會(huì)拿來判斷。

2.2 返回值

INSERT IGNORE 的返回值是一個(gè)整數(shù)，表示插入操作的結(jié)果：

• 返回值為 1：表示插入成功。
• 返回值為 0：表示插入操作被忽略（因?yàn)閿?shù)據(jù)重復(fù)）。
• 返回值為 n：表示成功插入了 n 行數(shù)據(jù)（在插入多行時(shí)）。

2.3 擴(kuò)展：查看警告信息

即使 INSERT IGNORE 忽略了插入操作，MySQL 仍然會(huì)生成一個(gè)警告（Duplicate entry）。你可以通過以下命令查看警告信息：

SHOW WARNINGS;

例如：

INSERT IGNORE INTO `user_info`(`id`, `risk_score`, `risk_level`, `register_time`, `created`, `updated`, `creator`, `updater`) VALUES ('11-000034639cc4408aba88358e73d48517', 10, 0, '2023-02-28 20:28:58', '2025-03-21 17:02:21', '2025-03-21 17:02:36', 'admin', 'admin');

SHOW WARNINGS;

輸出結(jié)果如下：

三、 使用場景

INSERT IGNORE 非常適用于以下場景：

3.1 避免重復(fù)數(shù)據(jù)插入

當(dāng)你需要插入大量數(shù)據(jù)，但不確定某些數(shù)據(jù)是否已經(jīng)存在時(shí)，INSERT IGNORE 可以幫助你避免重復(fù)插入，同時(shí)不會(huì)拋出錯(cuò)誤。例如：

INSERT IGNORE INTO `user_info`(`id`, `risk_score`, `risk_level`, `register_time`, `created`, `updated`, `creator`, `updater`) VALUES 
('11-000034639cc4408aba88358e73d48517', 10, 0, '2023-02-28 20:28:58', '2025-03-21 17:02:21', '2025-03-21 17:02:36', 'admin', 'admin'),
('11-000034639cc4408aba88358e73d48517', 10, 0, '2023-02-28 20:28:58', '2025-03-21 17:02:21', '2025-03-21 17:02:36', 'admin', 'admin');

執(zhí)行結(jié)果：

3.2 數(shù)據(jù)導(dǎo)入

在從其他數(shù)據(jù)源導(dǎo)入數(shù)據(jù)時(shí)，INSERT IGNORE 可以確保不會(huì)因?yàn)橹貜?fù)數(shù)據(jù)而導(dǎo)致插入失敗。例如：

INSERT IGNORE INTO `user_info`(`id`, `risk_score`, `risk_level`, `register_time`, `created`, `updated`, `creator`, `updater`) 
SELECT `id`, `risk_score`, `risk_level`, `register_time`, `created`, `updated`, `creator`, `updater` FROM asrc_user_info;

執(zhí)行結(jié)果：

3.3 日志記錄

在記錄日志或事件時(shí)，INSERT IGNORE 可以確保不會(huì)因?yàn)橹貜?fù)記錄而導(dǎo)致錯(cuò)誤。

這種場景主要在于一些日志重復(fù)上報(bào)的情況。

四、注意事項(xiàng)

雖然 INSERT IGNORE 非常方便，但在使用時(shí)還是要慎重，需要注意以下幾點(diǎn)：

4.1 不僅限于重復(fù)鍵錯(cuò)誤

INSERT IGNORE 不僅會(huì)忽略重復(fù)鍵錯(cuò)誤，還會(huì)忽略其他類型的錯(cuò)誤（如外鍵約束錯(cuò)誤）。因此，如果你只想忽略重復(fù)鍵錯(cuò)誤，而希望其他錯(cuò)誤拋出異常，可以使用 ON DUPLICATE KEY UPDATE 或其他機(jī)制。

4.2 性能影響

雖然 INSERT IGNORE 的性能通常較高，但在插入大量數(shù)據(jù)時(shí)，仍然需要注意性能問題。如果表中存在大量唯一索引，可能會(huì)導(dǎo)致插入操作變慢。在這種情況下，可以考慮批量插入或優(yōu)化表結(jié)構(gòu)。

4.3 注意：重要業(yè)務(wù)數(shù)據(jù)不能丟

因?yàn)槭歉鶕?jù)唯一鍵這些進(jìn)行去重，有一些場景可能是插入或者更新的重要數(shù)據(jù)，不能使用這個(gè)語句，切記。

五、與INSERT插入的區(qū)別

看到這里對(duì)他們之間的區(qū)別相信在腦海中已經(jīng)有一個(gè)概念了，下面我將用一個(gè)圖更加清晰的表示兩者的區(qū)別：

從上圖可以看出，他們兩個(gè)實(shí)際都是進(jìn)行了約束檢查的，只是一個(gè)返回錯(cuò)誤信息，停止語句的執(zhí)行，一個(gè)生成告警信息，跳過當(dāng)前行繼續(xù)執(zhí)行。

六、總結(jié)

INSERT IGNORE就像一個(gè)溫柔的數(shù)據(jù)庫管理員，遇到重復(fù)數(shù)據(jù)時(shí)不會(huì)大喊大叫拋出錯(cuò)誤，而是默默跳過，繼續(xù)處理下一條。

無論是批量導(dǎo)入數(shù)據(jù)、避免重復(fù)插入，還是處理日志重復(fù)上報(bào)，它都能輕松搞定。

不過，它也有小脾氣，比如會(huì)忽略所有錯(cuò)誤（不只是重復(fù)鍵），所以用的時(shí)候得小心。

總之，INSERT IGNORE 是數(shù)據(jù)庫操作中的“溫柔一刀”，幫你優(yōu)雅地解決問題，但別忘了檢查它的“副作用”哦！

到此這篇關(guān)于mysql中INSERT IGNORE的高效插入策略的文章就介紹到這了,更多相關(guān)mysql INSERT IGNORE插入內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論