错误原因
报错信息“Duplicate entry '1234' for key 'USER_INFO.PRIMARY'”,尝试插入一条数据时,主键值ID=1234,已经存在于表USER_INFO中。因为键(Primary Key)是表中的唯一标识符,每个记录必须有一个唯一的主键值,所以报错
例:
ID(主键) | USER_NAME | ADDRESS |
---|---|---|
1234 | 小红 | 武汉 |
5678 | 小白 | 长沙 |
执行如下插入sql后预期报错
INSERT INTO USER_INFO (ID, USER_NAME, ADDRESS) VALUES (1234, '小红', '武汉');
解决方案
1. INSERT IGNORE:忽略重复的插入操作,插入无效
机制:遇到主键冲突不进行任何操作
示例预期:
INSERT IGNORE USER_INFO (ID, USER_NAME, ADDRESS) VALUES (1234, '小红', '武汉');
ID(主键) | USER_NAME | ADDRESS |
---|---|---|
1234 | 小红 | 武汉 |
5678 | 小白 | 长沙 |
2.REPLACE INTO:可以在主键冲突时替换现有记录
机制:先尝试插入数据,如果发现主键冲突(或唯一索引冲突),则删除冲突的现有行,然后插入新行
示例预期:
REPLACE INTO USER_INFO (ID, USER_NAME, ADDRESS) VALUES (1234, '小绿', '北京');
ID(主键) | USER_NAME | ADDRESS |
---|---|---|
1234 | 小绿 | 北京 |
5678 | 小白 | 长沙 |
3. ON DUPLICATE KEY UPDATE:可以在主键冲突时更新现有记录
机制:先尝试插入数据,如果发现主键冲突(或唯一索引冲突),则执行update 操作,而不是删除和重新插入(设想场景:批量导入数据,如果表格中已存在主键相同信息,则以表格中信息为准,然后更新表格中的一个标识等)
INSERT INTO USER_INFO (ID, USER_NAME, ADDRESS) VALUES (1234, '小红', '武汉')
ON DUPLICATE KEY UPDATE USER_NAME = '小绿'
示例预期:
ID(主键) | USER_NAME | ADDRESS |
---|---|---|
1234 | 小绿 | 武汉 |
5678 | 小白 | 长沙 |