使用"insert into"語句進(jìn)行數(shù)據(jù)庫操作時(shí)可能遇到主鍵沖突，用戶需要根據(jù)應(yīng)用場景進(jìn)行忽略或者覆蓋等操作。總結(jié)下，有三種解決方案來避免出錯(cuò)。

1. insert ignore into
遇主鍵沖突，保持原紀(jì)錄，忽略新插入的記錄。
mysql> select * from device ;
+-------+--------+-------------+
| devid | status | spec_char   |
+-------+--------+-------------+
|     1 | dead   | zhonghuaren |
|     2 | dead   | zhong       |
+-------+--------+-------------+
2 rows in set (0.00 sec)

mysql> insert into device values (1,'alive','yangting');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert ignore  into device values (1,'alive','yangting');
Query OK, 0 rows affected (0.00 sec)

mysql> select * from device ;
+-------+--------+-------------+
| devid | status | spec_char   |
+-------+--------+-------------+
|     1 | dead   | zhonghuaren |
|     2 | dead   | zhong       |
+-------+--------+-------------+
2 rows in set (0.00 sec)
可見 insert ignore  into當(dāng)遇到主鍵沖突時(shí)，不更改原紀(jì)錄，也不報(bào)錯(cuò)

2. replace into
遇主鍵沖突，替換原紀(jì)錄，即先刪除原紀(jì)錄，后insert新紀(jì)錄

mysql> replace  into device values (1,'alive','yangting');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from device ;
+-------+--------+-----------+
| devid | status | spec_char |
+-------+--------+-----------+
|     1 | alive  | yangting  |
|     2 | dead   | zhong     |
+-------+--------+-----------+
2 rows in set (0.00 sec)

3. insert into ... ON DUPLICATE KEY UPDATE
其實(shí)這個(gè)是原本需要執(zhí)行3條SQL語句（SELECT,INSERT,UPDATE），縮減為1條語句即可完成。
即
IF (SELECT * FROM where 存在) {
    UPDATE  SET  WHERE ;
} else {
    INSERT INTO;
}
如：mysql> insert into device values (1,'readonly','yang') ON DUPLICATE KEY UPDATE status ='drain';
Query OK, 2 rows affected (0.00 sec)

上面語句偽代碼表示即為
if (select * from device where devid=1) {
    update device set status ='drain' where devid=1
} else {
    insert into device values (1,'readonly','yang')
}
很明顯，devid=1  是有的，這樣就執(zhí)行update操作
mysql> select * from device ;
+-------+--------+-----------+
| devid | status | spec_char |
+-------+--------+-----------+
|     1 | drain  | yangting  |
|     2 | dead   | zhong     |
+-------+--------+-----------+
2 rows in set (0.00 sec)
 

測試表：
CREATE TABLE `device` (
`devid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`status` enum('alive','dead','down','readonly','drain') DEFAULT NULL,
`spec_char` varchar(11) DEFAULT '0',
PRIMARY KEY (`devid`)
) ENGINE=InnoDB