insertintoselectmysql联合主键_insert时出现主键冲突的处理
⽅法
使⽤"insert into"语句进⾏数据库操作时可能遇到主键冲突，⽤户需要根据应⽤场景进⾏忽略或者覆盖等操作。总结下，有三种解决⽅案来避免出错。
测试表：
在linux中下载mysql时冲突是什么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
1. insert ignore into
遇主键冲突，保持原纪录，忽略新插⼊的记录。
mysql> select * fromdevice ;+-------+--------+-------------+
| devid | status | spec_char |
+-------+--------+-------------+
| 1 | dead | zhonghuaren |
| 2 | dead | zhong |
+-------+--------+-------------+
2 rows in set (0.00sec)
mysql> insert into device values (1,'alive','yangting');
ERROR1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert ignore into device values (1,'alive','yangting');
Query OK,0 rows affected (0.00sec)
mysql> select * fromdevice ;+-------+--------+-------------+
| 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.00sec)
mysql> select * fromdevice ;+-------+--------+-----------+
| 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 * fromdevice ;+-------+--------+-----------+
| devid | status | spec_char |
+-------+--------+-----------+
| 1 | drain | yangting |
| 2 | dead | zhong |
+-------+--------+-----------+
2 rows in set (0.00 sec)
如果更新多条记录可以向下⾯这样写
insert into device values(1, "dead", "ccc") ON DUPLICATE KEY UPDATE status ='drain', spec_char = "cccc"

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。