MySQL避免插入重復記錄的方法

Renate1621 8年前發布 | 18K 次閱讀 MySQL 數據庫服務器

mysql在存在主鍵沖突或者唯一鍵沖突的情況下，根據插入策略不同，一般有以下三種避免方法。

1、insert ignore

2、replace into

3、insert on duplicate key update

注意，除非表有一個PRIMARY KEY或UNIQUE索引，否則，使用以上三個語句沒有意義，與使用單純的INSERT INTO相同。

一、insert ignore

insert ignore會忽略數據庫中已經存在的數據(根據主鍵或者唯一索引判斷)，如果數據庫沒有數據，就插入新的數據，如果有數據的話就跳過這條數據.

Case：

表結構如下：

root:test> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3;
    +----+------+------+------+
    | id | c1   | c2   | c3   |
    +----+------+------+------+
    |  1 |    1 | a    |    1 |
    |  2 |    2 | a    |    1 |
    |  8 | NULL | NULL |    1 |
    | 14 |    4 | bb   | NULL |
    | 17 |    5 | cc   |    4 |
    +----+------+------+------+
    5 rows in set (0.00 sec)

測試插入唯一鍵沖突的數據

root:test> insert ignore into t3 (c1,c2,c3) values(5,'cc',4),(6,'dd',5);     Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

如下，可以看到只插入了(6,'dd',5)這條，同時有一條warning提示有重復的值。

root:test> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '5' for key 'uidx_c1' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 | a    |    1 |
|  2 |    2 | a    |    1 |
|  8 | NULL | NULL |    1 |
| 14 |    4 | bb   | NULL |
| 17 |    5 | cc   |    4 |
| 18 |    6 | dd   |    5 |
+----+------+------+------+
6 rows in set (0.00 sec)

重新查詢表結構，發現雖然只增加了一條記錄，但是AUTO_INCREMENT還是增加了2個（18變成20）

root:test> show create table t3\G
    *************************** 1. row ***************************
       Table: t3

     Create Table: CREATE TABLE `t3` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c1` int(11) DEFAULT NULL,
      `c2` varchar(20) DEFAULT NULL,
      `c3` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uidx_c1` (`c1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

二、replace into

replace into 首先嘗試插入數據到表中。如果發現表中已經有此行數據(根據主鍵或者唯一索引判斷)則先刪除此行數據，然后插入新的數據，否則，直接插入新數據。
使用replace into，你必須具有delete和insert權限

Case:

root:test> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3;
+----+------+--------+------+
| id | c1   | c2     | c3   |
+----+------+--------+------+
|  1 |    1 | cc     |    4 |
|  2 |    2 | dd     |    5 |
|  3 |    3 | qwewqe |    3 |
+----+------+--------+------+
3 rows in set (0.00 sec)

插入一條與記錄id=3存在唯一鍵（列c1）沖突的數據

root:test> replace into t3 (c1,c2,c3) values(3,'new',8);
Query OK, 2 rows affected (0.02 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 | cc   |    4 |
|  2 |    2 | dd   |    5 |
|  4 |    3 | new  |    8 |
+----+------+------+------+
3 rows in set (0.00 sec)

可以看到原有id=3，c1=3的記錄不見了，新增了一條id=4，c1=3的記錄.

replace into語句執行完會返回一個數，來指示受影響的行的數目。該數是被刪除和被插入的行數的和,上面的例子中2 rows affected .

三、insert on duplicate key update

如果在insert into 語句末尾指定了on duplicate key update，并且插入行后會導致在一個UNIQUE索引或PRIMARY KEY中出現重復值，則在出現重復值的行執行UPDATE；如果不會導致重復的問題，則插入新行，跟普通的insert into一樣。
使用insert into，你必須具有insert和update權限
如果有新記錄被插入，則受影響行的值顯示1；如果原有的記錄被更新，則受影響行的值顯示2；如果記錄被更新前后值是一樣的，則受影響行數的值顯示0

Case:

root:test> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3; 
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 | fds  |    4 |
|  2 |    2 | ytu  |    3 |
|  3 |    3 | czx  |    5 |
+----+------+------+------+
3 rows in set (0.00 sec)

插入一條與記錄id=3存在唯一鍵（列c1）沖突的數據

root:test> insert into t3(c1,c2,c3) values (3,'new',5) on duplicate key update c1=c1+3;   
Query OK, 2 rows affected (0.01 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 | fds  |    4 |
|  2 |    2 | ytu  |    3 |
|  3 |    6 | czx  |    5 |
+----+------+------+------+
3 rows in set (0.00 sec)

可以看到，id=3的記錄發生了改變，c1=原有的c1+3,其他列沒有改變。

結論：

這三種方法都能避免主鍵或者唯一索引重復導致的插入失敗問題。
insert ignore能忽略重復數據，只插入不重復的數據。
replace into和insert ... on duplicate key update，都是替換原有的重復數據，區別在于replace into是刪除原有的行后，在插入新行，如有自增id，這個會造成自增id的改變；insert ... on duplicate key update在遇到重復行時，會直接更新原有的行，具體更新哪些字段怎么更新，取決于update后的語句。

來自：http://www.cnblogs.com/prayer21/p/6018864.html

本文由用戶 Renate1621 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1477985303611.html

MySQL 數據庫服務器

MySQL避免插入重復記錄的方法

一、insert ignore

Case：

二、replace into

Case:

三、insert on duplicate key update

Case:

結論：

相關經驗

相關資訊

相關文檔

目錄