Mysql 主键冲突解决方式

转自：https://blog.csdn.net/qq_35246620/article/details/72854271

当主键冲突时，可以选择更新或替换

 

主键冲突：

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| grade | varchar(20) | NO   | PRI | NULL    |       |
| room  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from my_class;
+-------+------+
| grade | room |
+-------+------+
| XXX   | A315 |
| YYY   | A215 |
+-------+------+
2 rows in set (0.00 sec)

mysql> insert into my_class values("XXX","B315");
ERROR 1062 (23000): Duplicate entry 'XXX' for key 'PRIMARY'

更新操作： 

insert into my_class values("XXX","B315") on duplicate key update room ="B315"; 

mysql> insert into my_class values("XXX","B315") on duplicate key update room ="B315";
Query OK, 2 rows affected (0.02 sec)

mysql> select * from my_class;
+-------+------+
| grade | room |
+-------+------+
| XXX   | B315 |
| YYY   | A215 |
+-------+------+

替换操作：

replace into my_class values("XXX","C315");

mysql> replace into my_class values("XXX","C315");
Query OK, 2 rows affected (0.11 sec)

mysql> select * from my_class;
+-------+------+
| grade | room |
+-------+------+
| XXX   | C315 |
| YYY   | A215 |
+-------+------+
2 rows in set (0.00 sec)