原文：http://www.cnblogs.com/prayer21/p/6018864.html
mysql在存在主鍵沖突或者唯一鍵沖突的情況下，根據(jù)插入策略不同，一般有以下三種避免方法。
1、insert ignore
2、replace into
3、insert on duplicate key update
注意，除非表有一個PRIMARY KEY或UNIQUE索引，否則，使用以上三個語句沒有意義，與使用單純的INSERT INTO相同。
insert ignore會忽略數(shù)據(jù)庫中已經(jīng)存在的數(shù)據(jù)(根據(jù)主鍵或者唯一索引判斷)，如果數(shù)據(jù)庫沒有數(shù)據(jù)，就插入新的數(shù)據(jù)，如果有數(shù)據(jù)的話就跳過這條數(shù)據(jù).
表結構如下：
root:test> show create table t3\G*************************** 1. row *************************** Table: t3Create 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=utf81 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)
測試插入唯一鍵沖突的數(shù)據(jù)
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)
重新查詢表結構，發(fā)現(xiàn)雖然只增加了一條記錄，但是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)
root:test> show create table t3\G*************************** 1. row *************************** Table: t3Create 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=utf81 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）沖突的數(shù)據(jù)
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語句執(zhí)行完會返回一個數(shù)，來指示受影響的行的數(shù)目。該數(shù)是被刪除和被插入的行數(shù)的和,上面的例子中2 rows affected .
root:test> show create table t3\G*************************** 1. row *************************** Table: t3Create 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=utf81 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）沖突的數(shù)據(jù)
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的記錄發(fā)生了改變，c1=原有的c1+3,其他列沒有改變。
