删除具有唯一索引的重复项
我在两个表字段A,B,C,D之间插入,相信我在A,B,C,D上创建了唯一索引以防止重复项。然而,我不知何故只是对这些做了一个正常的索引。所以重复插入。它是2000万条记录表。删除具有唯一索引的重复项
如果我将现有的索引从正常状态更改为唯一状态,或者只是为A,B,C,D添加新的唯一索引,那么是否会删除重复项或将添加失败,因为存在唯一记录?我会测试它,但它是30万条记录,我不想把桌子搞乱或复制它。
如果你在你的表格重复和使用
ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
查询将失败,错误1062(重复键)。
但是如果你使用IGNORE
ALTER IGNORE TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
的副本将被删除。但文件没有指定哪些行将被保留:
IGNORE
是一个MySQL扩展到标准SQL。它控制ALTER TABLE
如果新表中的唯一键上有重复项,或者如果启用严格模式时发生警告,将如何工作ALTER TABLE
。如果IGNORE
不是 指定,则复制被中止并回滚,如果重复键错误 发生。如果指定了IGNORE
,则只有一行用于具有唯一密钥上 重复项的行。其他冲突的行被删除。 不正确的值被截断为最接近匹配的可接受的 值。
从MySQL 5.7.4开始,ALTER TABLE的IGNORE子句被删除, 的使用产生错误。
如果你的版本是5.7。4或更高版本 - 您可以:
- 将数据复制到临时表(它不需要是临时性的技术)。
- 截断原始表格。
- 创建唯一索引。
- 然后将数据复制回
INSERT IGNORE
(仍然可用)。
CREATE TABLE tmp_data SELECT * FROM mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
INSERT IGNORE INTO mytable SELECT * from tmp_data;
DROP TABLE tmp_data;
如果使用
IGNORE
改性剂,在执行INSERT
声明中出现的错误被忽略。例如,如果没有IGNORE
,表 重复表UNIQUE
索引或PRIMARY KEY
表 中的值会导致重复键错误,并且语句会中止。使用IGNORE
,该行将被丢弃并且不会发生错误。忽略错误 改为生成警告。
另见:INSERT ... SELECT Syntax和Comparison of the IGNORE Keyword and Strict SQL Mode
如果您认为会有重复项,则添加唯一索引将会失败。 先检查什么复制有:
select * from
(select a,b,c,d,count(*) as n from table_name group by a,b,c,d) x
where x.n > 1
这可能是在20M行的昂贵的查询,但将让你的所有重复键,这将阻止您添加的主要指标。 如果您在子查询中执行某处操作,则可以将其拆分为更小的块:where a='some_value'
对于检索到的记录,您必须更改某些内容才能使行具有唯一性。如果这样做(查询返回0行),您应该可以安全地添加主索引。
要回答您的问题 - 在具有重复值的列上添加UNIQUE
约束将引发错误。
例如,你可以试试下面的脚本:
CREATE TABLE `USER` (
`USER_ID` INT NOT NULL,
`USERNAME` VARCHAR(45) NOT NULL,
`NAME` VARCHAR(45) NULL,
PRIMARY KEY (`USER_ID`));
INSERT INTO USER VALUES(1,'apple', 'woz'),(2,'apple', 'jobs'),
(3,'google', 'sergey'),(4,'google', 'larry');
ALTER TABLE `USER`
ADD UNIQUE INDEX `USERNAME_UNIQUE` (`USERNAME` ASC);
/*
Operation failed: There was an error while applying the SQL script to the database.
ERROR 1062: Duplicate entry 'apple' for key 'USERNAME_UNIQUE'
*/
而是不理你可以使用对重复密钥更新,这将让你控制哪些值为准。
您需要使用'IGNORE'关键字 - 否则会失败。在一个小测试桌上测试它。 –
“IGNORE是标准SQL的MySQL扩展,它控制着ALTER TABLE如何在新表中的唯一键上存在重复或如果启用严格模式时发生警告......” - [ALTER TABLE语法](http: //dev.mysql.com/doc/refman/5.7/en/alter-table.html) –
@PaulSpiegel这很有道理。我尝试复制Table1,仅结构,并添加一个唯一的索引,然后在原始索引表和新索引表之间插入,但确实失败。我这样做是因为在30万条记录上更改或添加独特索引需要很长时间。因此,现在我在“插入”后添加了“忽略”,它效果很好。 – user3649739