如何去除表格中的重复结果 [英] How to get rid of duplicate results in a table

问题描述

我有一个有一些重复结果的表格.例如:

I have a table that has some duplicate results. For example:

`person_url`    `movie_url`
1                2
1                2
2                3

会变成-->

`person_url`    `movie_url`
1                2
2                3

我知道如何通过创建新表来做到这一点，

I know how to do it by creating a new table,

create table tmp_credits (select distinct * from name);

然而，它是一个非常大的表，我有几个索引需要重新创建.我将如何就地进行这种转换，即不创建新表?

However, it is a pretty large table and I have a couple indexes on it which will need to be re-created. How would I do this transformation in place, that is, without creating a new table?

推荐答案

您可以使用 IGNORE 关键字在表的列上添加 UNIQUE 索引:

You can add a UNIQUE index over your table's columns using the IGNORE keyword:

ALTER IGNORE TABLE name ADD UNIQUE INDEX (person_url, movie_url);

如手册中所述:

IGNORE 是标准 SQL 的 MySQL 扩展.它控制 ALTER TABLE 如何工作，如果新表中的唯一键有重复，或者在启用严格模式时出现警告.如果未指定 IGNORE，则复制将中止并在出现重复键错误时回滚.如果指定了 IGNORE，则只有第一行用于在唯一键上具有重复项的行.其他冲突行被删除.不正确的值被截断为最接近匹配的可接受值.

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.

这也将防止将来添加重复项.

This will also prevent duplicates from being added in the future.

这篇关于如何去除表格中的重复结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！