Navicat 博客

2017 年 12 月 27 日,由 Robert Gravelle 撰写

大多数重复记录属于这两类:重复含义和非唯一键。我们在“如何在 MySQL 中发现和删除具有重复含义的值”文章中处理了重复含义的记录。在今天的后续文章中,我们将讨论如何识别非唯一键。这是在同一个表中的两条记录具有相同的键,但可能有或可能没有不同的值和含义。

这是怎么发生的?

即使是设计良好的数据库也可能会有非唯一键的重复项。它通常是由从外部源(如 text、csv 或 excel 文件)以及数据源导入的数据导致的。如果要组合数据生成新键,即使合并来自两个不同数据库的数据,也可能会导致重复键的问题。当然这假设了新鍵列支持非唯一值。例如,连接两个数字以生成新键就可能会出现问题:

Key 1     Key 2     New Key
--------------------------
10 25 1025
102 5 1025 !!!

示例表

在支持复杂系统的数据库中,防止重复键发生并不总是可行的。重要的是能够在污染数据之前快速有效地处理它们。

让我们由从重叠键中分开真重复值开始。

这表合并了两个演员的数据源。你会注意到有几个重复的名字,特别是“JENNIFER DAVIS”和“NICK WAHLBERG”:

id      first_name      last_name
--------------------------------------
10 PENELOPE GUINESS
12 NICK WAHLBERG
14 ED CHASE
22 JENNIFER DAVIS
23 JOHNNY LOLLOBRIGIDA
27 BETTE NICHOLSON
34 GRACE MOSTEL
41 NICK WAHLBERG
39 JOE SWANK
23 CHRISTIAN GABLE
22 JENNIFER DAVIS

NICK WAHLBERG 是重复意义的一个实例,我们在上一篇文章中探讨过。另一方面,JENNIFER DAVIS 出现在两条记录中,並有相同的键 22。还有另一个重复的键 #23,是两条不相关的演员的记录:“JOHNNY LOLLOBRIGIDA”和“CHRISTIAN GABLE”。关于 22 和 23 的重复键,第一个是真正的重复,而第二个只需要为其中一条记录生成新键就可解决。

识别和计算重复项

下面的查询可标识上表中有相同 ID 的所有记录。我建议使用 MySQL 的 group_concat() 函数将重复的行组在一行:

SELECT
  COUNT(*) as repetitions,
  group_concat(id, ' (', last_name, ', ', first_name, ') ' SEPARATOR ' | ')
    as row_data
FROM amalgamated_actors
GROUP BY id
HAVING repetitions > 1;

Repetitions      row_data
-------------------------------------------------------------
2 22 (DAVIS, JENNIFER) | 22 (DAVIS, JENNIFER)
2 23 (LOLLOBRIGIDA, JOHNNY) | 23 (GABLE, CHRISTIAN)

如果你想同时查找所有重复项(即重复含义和非唯一键重复项),你可以将上述查询与使用 UNION 运算符检查重复名的查询组合:

SELECT
  COUNT(*) as repetitions,
  group_concat(id, ' (', last_name, ', ', first_name, ') ' SEPARATOR ' | ')
    as row_data
FROM amalgamated_actors
GROUP BY id
HAVING repetitions > 1
UNION
SELECT
  COUNT(*) as repetitions,
  group_concat(id, ' (', last_name, ', ', first_name, ') ' SEPARATOR ' | ')
    as row_data
FROM amalgamated_actors
GROUP BY last_name, first_name
HAVING repetitions > 1;

这高亮显示了一个结果集中的所有重复项:

Repetitions      row_data
-------------------------------------------------------------
2 22 (DAVIS, JENNIFER) | 22 (DAVIS, JENNIFER)
2 23 (LOLLOBRIGIDA, JOHNNY) | 23 (GABLE, CHRISTIAN)
2 41 (WAHLBERG, NICK) | 12 (WAHLBERG, NICK)

总结

编写查询以识别 MySQL 中的重复键是相对简单的,因为你只需要在键字段上进行分组并包含“Having COUNT(*) > 1”子句。在以后的文章中,我们将评估一些删除重复行和更新密钥的方法。

Navicat 文章
频道条目
分享
文章归档