数据库中重复行/XML节点识别与清理

• 下载脚本源代码

引言

大家都知道重复行会带来很多问题

• 错误的输出
• 难以捉摸的异常
• 工作/已测试的代码失败
• 数据库大小增加
• 应用程序崩溃
• 等等。

在本文中，我将解释如何识别数据库表中的重复行并进行清理。我还将介绍具有相同问题的 XML 类型字段中的重复 XML 节点。在实际生活中，存在各种独特的场景，需要采用各种技术来处理所有这些场景。在一篇文章中涵盖所有场景并提供解决方案是困难的。我在这里的目的是尝试理解重复行/XML节点问题及其解决方案的基本部分，以便在不同场景下能够适当地处理它们。

背景

没有人会故意在数据库中创建重复行。当表没有主键/唯一键、没有 proper 数据验证，并且业务逻辑发生更改但现有数据的校正未正确完成时，很容易创建重复行。除此之外，还有一些场景会导致创建重复行：

• 从其他数据库导入数据，源数据库包含重复行。
• 重构/重新组织数据库时，可能会创建重复行。
• 现有应用程序错误。
• 数据录入操作录入了重复行。

识别唯一行和重复行

为了识别数据库表中的唯一行和重复行，我创建了一个示例表和行，将这些行插入到该表中，并执行 SQL 查询。我的表结构包含三个字段：

1. ID
2. 名称
3. DateOfBirth

create table MyTestTable(Id int, Name varchar(50), DateOfBirth datetime); 
insert into MyTestTable(Id, Name, DateOfBirth)
values(1, 'A', '01-Jan-2010'), (2, 'B', '01-Jan-2011'), (2, 'B', '01-Jan-2011'),
(3, 'C', '01-Jan-2012'),(4, 'D', '01-Jan-2013'),(4, 'D', '01-Jan-2013'); 

经过数据分析，可以清楚地看出 ID 为 1 和 3 的行是唯一的，其余的都是重复的。识别唯一行：

select max(Id) as Id, Name, DateOfBirth FROM MyTestTable
group by Name, DateOfBirth
having count(*) = 1; 

在查询中，按字段（Name 和 DateOfBirth）分组，并使用 Max 聚合函数（如果表中存在更多列，则使用 ID 字段；然后需要按所有字段分组）。如果使用 Min 而不是 Max，结果将相同。我们使用 count(*) = 1 子句过滤行。实际上，我们请求数据库给出每个组只包含一条记录的组。

现在，对于表中的重复行，只需更改筛选子句 count(*) > 1。

select max(Id) as Id, Name, DateOfBirth FROM MyTestTable
group by Name, DateOfBirth
having count(*) > 1; 

了解重复行的频率

select id,name,dateofbirth, count(*) as Total
  from MyTestTable
  group by id,name,dateofbirth
  having count(*) > 1

现在我将解释一个实际场景。在示例行中，我没有在 DateOfBirth 字段中插入时间。但通常 datetime 字段会包含日期和时间。为了更好地理解这一点，我首先清空表，然后再次插入带有时间的示例行。

truncate table  MyTestTable;--delete all rows from the table   
insert into MyTestTable (Id, Name, DateOfBirth)--insert sample data again
values (1, 'A', '01-Jan-2010 12:00:01'), (2, 'B', '01-Jan-2011 12:00:05'), (2, 'B', '01-Jan-2011 12:00:06'),
(3, 'C', '01-Jan-2012 12:00:01'),(4, 'D', '01-Jan-2013 12:00:07'),(4, 'D', '01-Jan-2013 12:00:08');  

在分析新的行集后，您会发现由于时间的存在，没有重复行。添加时间会使每一行都变得唯一。

再次运行重复行识别查询

select max(Id) as Id, Name, DateOfBirth FROM MyTestTable
group by Name, DateOfBirth
having count(*) > 1 

它将返回空结果。这证明表中不存在重复行。但是，在考虑重复行时，通常出于业务策略的原因，会忽略时间。因此，我重写了查询来考虑这一点：

select max(Id) as Id, Name, max(DateOfBirth) as DateOfBirth FROM MyTestTable
group by  Name, convert(varchar(20),DateOfBirth, 104)
having count(*) > 1 

忽略 datetime 中的时间，我使用了 T-SQL 的 Convert 标量函数和 Max 聚合函数。

删除重复行

如果数据库包含重复行，我们必须进行清理。之前我们已经识别了重复行。现在我们将开始清理操作。我们可能会认为删除所有重复行非常简单直接。请看下面的 SQL：

delete from MyTestTable
where id in(select max(Id) FROM MyTestTable
group by  Name, convert(varchar(20),DateOfBirth, 104)
having count(*) > 1)

现在没有重复行了。但它也会删除真实数据。但这是如何发生的？在数据分析后，您会发现 ID 为 2 和 4 的行各有两个重复行，并且第一行是有效的。下一个实际上是重复的。

所以，我们应该保留每个组的第一行，删除后面的。删除查询会很棘手。我们需要借助 CTE（公用表表达式）和 row_number 函数。CTE 将帮助指向特定行，而 row_number 函数将为每一行创建一个唯一的行 ID，以便我们可以唯一地跟踪每一行。删除查询如下：

WITH cte AS
(
    select row_number() over(order by id asc) as rowid, Id, Name, DateOfBirth 
    from MyTestTable
)
DELETE FROM cte WHERE rowid in (select max(rowid) from cte  
group by id, Name, DateOfBirth having count(*) > 1);

执行上述查询后，输出如下：

我想说一下删除查询。该表没有像主键那样的唯一字段来标识每一行。所以我们使用 row_number 函数创建了一个动态唯一字段。rowid 字段实际上是虚拟的，没有物理存在。所以我们不会直接基于该字段从表中执行删除语句。我们使用 CTE 基于虚拟字段执行删除语句，并从表中清理重复行。

请稍等！故事还没结束。在我们的示例中，行 2 和 4 各有两个行。因此，删除查询只会删除下一个行。如果找到两个以上的重复行，会发生什么？查询只会删除最后一个重复行，尽管我使用了 Max 聚合函数。

select max(rowid) from cte   

我们知道问题还没有解决，但我们离解决方案已经很近了。

我们需要一个完整的 T-SQL 查询来删除除第一行之外的所有重复行。

解决方案-1

while (1=1)--infinite loop start
begin
    WITH cte AS
    (
    select row_number() over(order by id asc) as rowid, Id, Name, DateOfBirth from MyTestTable
    )
    delete from cte where rowid in (select max(rowid) from cte group by id, Name, DateOfBirth having count(*) > 1);
        --inifinite loop finish when no duplicate rows found
    if @@ROWCOUNT = 0 break;    
end 

开始一个无限循环来删除重复行，直到找到单个重复行。当 SQL Server 全局变量 @@RowCount 返回 0 时，无限循环将结束。当源表中未找到重复行时，它将返回零。

解决方案-1 工作正常，但代码不是很优雅，而且因为使用了无限循环而有点复杂。许多人不喜欢它，因为它缺乏简洁性。

解决方案-2

WITH cte AS
(
    select row_number() over (partition by id,Name,
       DateOfBirth order by id asc) as rowid,      Id, Name, DateOfBirth 
    from MyTestTable
)
DELETE FROM cte WHERE rowid > 1; 

解决方案-2 比解决方案-1 更简单。虚拟字段 Rowid 是按组生成的，使用 row_number 函数，并将 **id、name、DateOfBith** 字段视为一个组。生成的 rowid 从每个组的 1 开始。新的过滤条件是 **rowid > 1**。这意味着每个组只保留 ID 为 1 的行，其余的将被删除。

XML 节点重复识别与删除

SQL Server 中的 XML 数据类型是在 2005 版本中引入的。我们应该使用 XML 类型字段有很多原因：

• 将多个字段的值存储到单个字段中。
• 同时存储模式和数据。
• 存储层次结构数据。
• 在运行时定义了带有模式的动态数据。
• 存储可以在运行时配置的模式。
• 存储各种数据模板。
• 数据需要根据任何固定模式进行验证。
• 还有很多

在 XML 字段内部，由于各种原因可能会创建重复节点。识别和清理 XML 字段中的重复节点与识别和清理表中的重复行同样重要，这样可以确保数据的准确性，并提供处理各种异常的方法。

识别重复 XML 节点

为了准确地可视化问题和解决方案，我们创建了一个名为 MyTestTable2 的示例表，该表有两个列：

1. Id (数据类型-int)
2. Data (数据类型-xml)

create table MyTestTable2(Id int, Data xml);  
declare @data1 xml=
'<employee>
    <code>1</code>
    <name>A</name>
    <code>1</code>
</employee>',
@data2 xml='<employee>
    <code>2</code>
    <code>2</code>
    <name>B</name>
</employee>',
@data3 xml='<employee>
    <code>3</code>
        <name>C</name>
</employee>';

insert into MyTestTable2(Id, data) values(1, @data1), (2, @data2), (3, @data3); 

现在，带有数据的表看起来像这样：

select all id, data  from MyTestTable2;

经过数据分析，我们看到只有 ID 为 1 的行包含一个名为 code 的重复 XML 节点，该节点在 employee 元素中出现了两次（在 name 节点之后重复）。以下 SQL 有助于识别包含重复 XML 节点的行。

select Id, Data from MyTestTable2 where data.value('count(/employee/code)', 'int') > 1; 

它将返回 ID 为 1 的单行。因此，我们了解到该查询对于识别包含重复 XML 节点的行是正确的。

清理重复 XML 节点

以下查询将从源表中删除重复节点。

update MyTestTable2 set 
data.modify('delete /employee/code[position()>1]')  
where data.value('count(/employee/code)', 'int') > 1;

执行上述查询后，输出如下：

select id, data from MyTestTable2; 

现在我们看到表中 Data 字段（XML 类型）中没有重复的 XML 节点了。

何时删除？

应尽快识别和删除重复行。如果太晚，该重复行可能已被用作引用行（外键）。如果它被用作引用键，则会引发参照完整性错误，删除操作会变得复杂。如果您遇到这种情况，则首先需要更新引用表/引用字段为一个有效键，然后进行删除。

无论如何，能否连续搜索和删除重复行？

我们可以编写一个存储过程来识别重复行/XML节点并删除它们，并定期执行该存储过程。我们可以使用以下工具定期执行存储过程：

• SQL Server Agent
• Windows 服务
• Windows 任务计划程序

如果您选择 Windows 服务或 Windows 任务计划程序，则需要创建一个控制台/Windows 服务应用程序，并在该应用程序中使用 ADO.NET 或 Enterprise Library 来访问您的数据库并执行存储过程。如果您需要使用 Windows 服务，您还可以使用 Windows Timer 对象进行定期执行。我个人选择 Windows 任务计划程序。它更简单且易于配置。

任何注意事项？

重复删除存储过程（SP）必须经过充分测试。您应该花足够的时间进行proper 测试。如果可能，最好寻求测试团队/第三方人员的帮助。

删除操作非常危险，尤其是在生产数据库上进行时。客户绝不接受数据丢失。如果您在删除重复行的同时还删除了真实数据，这是不可接受的。因此，在删除操作之前，您必须确保源数据已得到proper 备份，以便在发生任何错误时可以恢复数据。

关注点

我尝试解释和展示在数据库以及 XML 类型字段中的节点中创建重复记录的各种方法。重复数据会产生维护开销并导致各种类型的错误。因此，我们需要定期清理生产数据库，特别是在维护生命周期较长的项目/产品中。