mysql - 不对多列强制执行唯一索引 - MySQL 与 Oracle 的不同行为

Question

这个简单的脚本:

create table test (a integer not null, b integer, c integer not null);
create unique index test1 on test (a, b, c);
insert into test values(1, null, 1);
insert into test values(1, null, 1);
select * from test;

在 MySQL 上成功运行，在 Oracle 上运行失败并出现 ORA-0001“违反唯一约束”。

我不确定标准对多个空列上的唯一索引的说法是什么，但 MySQL 的行为可能与 Oracle 类似。

另见 http://lists.mysql.com/mysql/183630

亚历山大。

Answer 1

MySQL 在设计上允许在具有 UNIQUE 索引的列中使用多个 NULL 值。

MySQL 上的 BDB 存储引擎 是个异常(exception)，它不允许在一个列中有多个 NULL 值，在它上面有 UNIQUE 索引。

另一方面，Oracle 在 NULL 值方面表现不同。当您在 Oracle 中的单个列上创建 UNIQUE 索引时，它将允许您拥有多个 NULL 值，因为 NULL 基本上意味着 < em>未知值，因此两个 NULL 值无法相互比较。不仅如此，在 Oracle 的情况下，NULL 值不存储在索引中。这意味着，当您在 Oracle 中的多个列上创建一个 UNIQUE 索引时，其中两列是 NOT NULL，一列是 NULLABLE，它不允许您插入两条具有相同值的记录，即使其中一列包含 NULL。

考虑一下:

CREATE TABLE test (a NUMBER NOT NULL,
                   b NUMBER,
                   c NUMBER NOT NULL
                  );

INSERT INTO test VALUES (1, NULL, 1);
1 rows inserted.

INSERT INTO test VALUES (1, NULL, 1);

SQL Error: ORA-00001: unique constraint (RQ4151.UQ_TEST) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

发生这种情况是因为 Oracle 没有在索引中存储 NULL 值，因此尝试比较 NOT NULL 列值在索引中的唯一性，但失败了，导致错误被标记。