原文出处:http://hi.baidu.com/ytjwt/blog/item/1ccc2c26022b0608908f9d8c.html
使用"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)
mysql> select * from device ; +-------+--------+-------------+ | devid | status | spec_char | +-------+--------+-------------+ | 1 | dead | zhonghuaren | | 2 | dead | zhong | +-------+--------+-------------+ 2 rows in set (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)
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 其实这个是原本需要执行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 是有的,这样就执行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
|