使用"insert into"語句進行資料庫操作時可能遇到主鍵沖突,使用者需要根據應用場景進行忽略或者覆寫等操作。總結下,有三種解決方案來避免出錯。
1. insert ignore into遇主鍵沖突,保持原紀錄,忽略新插入的記錄。
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)
可見 insert ignore into當遇到主鍵沖突時,不更改原紀錄,也不報錯
2. replace into遇主鍵沖突,替換原紀錄,即先删除原紀錄,後insert新紀錄
mysql> replace into device values (1,'alive','yangting');
Query OK, 2 rows affected (0.00 sec)
+-------+--------+-----------+
| devid | status | spec_char |
| 1 | alive | yangting |
| 2 | dead | zhong |
3. insert into ... ON DUPLICATE KEY UPDATE其實這個是原本需要執行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';
上面語句僞代碼表示即為
if (select * from device where devid=1) {
update device set status ='drain' where devid=1
insert into device values (1,'readonly','yang')
很明顯,devid=1 是有的,這樣就執行update操作
| 1 | drain | yangting |
測試表:
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