mysql唯一索引已有键值冲突解决办法

建唯一索引： alter ignore table tmp_qw2 add unique key uk_uid(user_id); IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict



建唯一索引：

alter ignore table tmp_qw2 add unique key uk_uid(user_id);

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

此方法注意点：alter ignore的语法不支持innodb

set old_alter_table = 1;

ALTER IGNORE TABLE tableA ADD UNIQUE INDEX idx_col1_u (col1) 

具体原理：http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_old_alter_table

插入时冲突处理：

insert into t   ... on duplicate key update c1=values

1. 如果存在唯一索引冲突，则更新冲突的记录。当多个唯一索引记录冲突时，也只更新一个记录（挑选规则不确定)

小微助手

微信推出的一款专注于提升桌面效率的助手型AI工具

249

查看详情

2. audo_increment自增字段会出现间隙

可设置：innodb_autoinc_lock_mode=0

3 避免在联合唯一索引中使用，因为它只会更新符合条件：a=1 or b=2 limit 1的第一条数据；至少在5.7版本依然如此，官方文档原文如下：

If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
这一点在MariaDB并不适用，至少从MariaDB 5.5.32版本开始已经修正此bug或已调整实现逻辑。

4. 在含有多个values()一次性插入多条数据的时候，可以在update语句中用values()来指定字段，示例：

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

5  在含有auto_increment自增字段的表中，可以用LAST_INSERT_ID(id)函数来获取自增字段最新的值，示例：

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;