insert into select mysql联合主键_insert时出现主键冲突的处理方法

使用"insert into"语句进行数据库操作时可能遇到主键冲突，用户需要根据应用场景进行忽略或者覆盖等操作。总结下，有三种解决方案来避免出错。

测试表：

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"