修复DB重复条目（MySQL错误） [英] Fix DB duplicate entries (MySQL bug)

问题描述

我正在使用MySQL 4.1。某些表格中有重复的条目违背约束条件。

当我尝试分组行时，MySQL不会将这些行识别为类似。

示例：

表A具有唯一属性列

表中包含一行名称为哈希，一行名称相同但平方而不是？（我不能在此文本框中复制）

A按这两行分组返回2个单独的行

这会导致几个问题，包括无法导出和重新导入数据库的事实。在重新导入错误时提到插入失败，因为它违反了约束。

理论上我可以尝试导入，等待第一个错误，修复导入脚本和原始DB，并重复。有一种方法可以列出所有异常，或强制数据库重新检查约束（并列出所有的值/行）他们）？

如果可以帮助，我可以提供.MYD文件。

解决方案

我会假设这是一个MySQL 4.1随机错误。即使他们违反了一些MySQL的约束，索马里的价值观也没有任何特定的原因。 MySQL只是忽略这些违规行为。

为了解决我的问题，我将编写一个程序，试图将同一个表中的每一行数据进行树脂化（确切地说，另一个表格具有相同的特征）并记录每个失败的实例。

如果有人遇到同样的问题，有人找到更多的内容，我会将事件打开一段时间实用的解决方案。

I'm using MySQL 4.1. Some tables have duplicates entries that go against the constraints.

When I try to group rows, MySQL doesn't recognise the rows as being similar.

Example:

Table A has a column "Name" with the Unique proprety.
The table contains one row with the name 'Hach?' and one row with the same name but a square at the end instead of the '?' (which I can't reproduce in this textfield)
A "Group by" on these 2 rows return 2 separate rows

This cause several problems including the fact that I can't export and reimport the database. On reimporting an error mentions that a Insert has failed because it violates a constraint.

In theory I could try to import, wait for the first error, fix the import script and the original DB, and repeat. In pratice, that would take forever.

Is there a way to list all the anomalies or force the database to recheck constraints (and list all the values/rows that go against them) ?

I can supply the .MYD file if it can be helpful.

解决方案

I'll assume this is a MySQL 4.1 random bug. Somes values are just changing on their own for no particular reason even if they violates some MySQL constraints. MySQL is simply ignoring those violations.

To solve my problem, I will write a prog that tries to resinsert every line of data in the same table (to be precise : another table with the same caracteristics) and log every instance of failures.

I will leave the incident open for a while in case someone gets the same problem and someone else finds a more practical solution.

这篇关于修复DB重复条目（MySQL错误）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！