MySQL - 如何通过索引避免表中的重复记录？

Question

使用MySQL 5

我有一张这样的表：

date (varchar)
door (varchar)
shift (varchar)
route (varchar)
trailer (varchar)
+ other fields

这张表包含用户生成的内容（从另一个“主”表中复制而来），为了防止用户创建相同的数据超过1次，该表根据上述指定的字段创建了唯一索引。

问题在于“重复预防”索引不起作用。
用户仍然可以添加重复记录，却没有报错信息。

是因为我没有理解索引的工作原理所以出现了这个问题吗？

还是可能与主键字段（自动递增整数）发生冲突？

CREATE TABLE 如下：

CREATE TABLE /*!32312 IF NOT EXISTS*/ "tableA" (
"Date" varchar(12) default NULL,
"door" varchar(12) default NULL,
"Shift" varchar(45) default NULL,
"route" varchar(20) default NULL,
"trailer" varchar(45) default NULL,
"fieldA" varchar(45) default NULL,
"fieldB" varchar(45) default NULL,
"fieldC" varchar(45) default NULL,
"id" int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY  ("id"),
UNIQUE KEY "duplicate_preventer" ("Date","door","Shift","route","trailer"),

一行被复制是指：

date       door  shift      route    trailer

10/4/2009  W17   1st Shift  TEST-01  NULL
10/4/2009  W17   1st Shift  TEST-01  NULL

- John M

---

请展示CREATE TABLE语句。 - dnagirl

你可以发一些你认为是重复的行吗？ - Juha Syrjälä

你正在使用哪个数据库引擎，例如InnoDB或MyISAM？ - Juha Syrjälä

InnoDB 是数据库引擎。一行重复的内容可能是： 日期、门、班次、路线、拖车 10/4/2009，W17，第一班次，测试-01，NULL 10/4/2009，W17，第一班次，测试-01，NULL - John M

相关链接：http://www.xaprb.com/blog/2009/09/12/the-difference-between-a-unique-index-and-primary-key-in-mysql/ - John M

Answer 1

用户仍然可以添加重复记录而不报错。

"重复记录"是什么意思？

根据排序规则、大小写和音调等因素，'test'和'TEST'不会被视为重复。

请问您能否发布SHOW CREATE TABLE mytable的结果？

另外，请运行以下查询：

SELECT  date, door, shift, route, trailer
FROM    mytable
GROUP BY
        date, door, shift, route, trailer
HAVING  COUNT(*) > 1

如果返回行，则问题在于索引; 如果没有返回行，则问题出在“重复”定义上。

更新：

您的列允许NULL。

从UNIQUE索引的角度来看，MySQL中的NULL值不被视为重复：

CREATE TABLE testtable (door VARCHAR(20), shift VARCHAR(15), UNIQUE KEY (door, shift));

INSERT
INTO    testtable
VALUES
('door', NULL),
('door', NULL);

SELECT  door, shift
FROM    testtable
GROUP BY
        door, shift
HAVING  COUNT(*) > 1;

根据文档：

UNIQUE索引创建了一个约束，使得索引中的所有值必须是不同的。如果您尝试添加一个关键值与现有行匹配的新行，则会出现错误。除了BDB存储引擎外，此约束不适用于NULL值。对于其他引擎，UNIQUE索引允许包含NULL的列具有多个NULL值。如果在UNIQUE索引中为列指定前缀值，则列值必须在前缀内唯一。

- Quassnoi

---

它确实返回了重复的行 - 所以问题在于索引。 - John M

同样，对我而言，重复记录具有完全相同的所有字段（在索引中）。 - John M

请问您能否发布 SHOW CREATE TABLE mytable 的输出结果？问题肯定出在索引上，它可能没有被创建为 UNIQUE 或者根本没有被创建。 - Quassnoi

我发布了创建表格的语法。 - John M

问题出在NULL值上。 - Quassnoi

Answer 2

您确定您正在使用唯一索引而不是普通索引吗？

create unique index uix on my_table (date, door, shift, route, trailer);

这种类型的索引只是确保字段组合唯一，例如，如果每行的门字段不同，则可以有多个重复日期。差异可能很难发现，例如值末尾的空格或大小写差异。

更新：您的唯一索引似乎没有问题。问题出在其他地方。

- Juha Syrjälä

---

通常我会通过MySQL Administrator或者在这种情况下使用HeidiSQL来创建索引。我会设置所需的列作为索引，然后选择“唯一”选项。 - John M

Answer 3

我认为您想在不希望重复的字段上创建唯一约束。这将进而创建一个唯一索引。

像这样：

ALTER TABLE YourTable ADD CONSTRAINT uc_yourconstraintname UNIQUE (date, door, shift, route, trailer)

- Jim B