转载

MySQL和Oracle中的唯一性索引从差别(r12笔记第83天)

今天在修复MySQL数据的时候，发现一个看起来“奇怪”的问题。

有一个表里存在一个唯一性索引，这个索引包含3个列，这个唯一性索引的意义就是通过这3个列能够定位到具体1行的数据，但是在实际中却发现这个唯一性索引还是有一个地方可能被大家忽略了。

我们先来看看数据的情况。

CREATE TABLE `test_base_data` (
`servertime` datetime DEFAULT NULL COMMENT '时间',
`appkey` varchar(64) DEFAULT NULL,
...
`timezone` varchar(50) DEFAULT NULL COMMENT '时区',
UNIQUE KEY `servertime_appkey_timezone` (`servertime`,`appkey`,`timezone`),
KEY `idx_ccb_r_b_d_ak_time` (`servertime`,`appkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表里的数据量在300万左右

> select count(*)from test_base_data;
+----------+
| count(*) |
+----------+
| 3818630 |
+----------+

我在分析一个问题的时候，发现按照目前的情况，似乎主键和唯一性索引有一点差别（当然回过头来看这个问题本身就很明确了）。

于是我尝试删除这个唯一性索引，转而创建一个主键，但是这个操作竟然抛出了数据冲突的的错误。

> alter table test_base_data add primary key `servertime_appkey_timezone` (`servertime`,`appkey`,`timezone`);
ERROR 1062 (23000): Duplicate entry '2017-05-09 13:15:00-1461048746259-' for key 'PRIMARY'

数据按照appkey 1461048746259来过滤，得到的一个基本情况如下：

> select servertime,appkey,timezone from ccb_realtime_base_data limit 5;
+---------------------+---------------+----------+
| servertime          | appkey        | timezone |
+---------------------+---------------+----------+
| 2017-05-09 20:25:00 | 1461048746259 | NULL     |
| 2017-05-09 13:15:00 | 1461048746259 | NULL     |
| 2017-05-09 19:00:00 | 1461048746259 | NULL     |
| 2017-05-09 17:00:00 | 1461048746259 | NULL     |
| 2017-05-09 20:30:00 | 1461048746259 | NULL     |
+---------------------+---------------+----------+

单纯这样看，看不出什么问题来，但是当我有count来得到重复数据的时候，着实让我惊呆了。

> select count(1) from ccb_realtime_base_data where servertime ='2017-05-09 13:15:00' and appkey='1461048746259';
+----------+
| count(1) |
+----------+
| 709 |
+----------+

这一行记录，在这个表里竟然有重复的数据达到700多个。

按照这个情况，表里的数据缺失有大的问题，但是为什么唯一性索引就查不出来呢。

这一点上，Oracle和MySQL的立场是一致的，那就是主键和唯一性索引的差别，出了主键的根红苗正，主键是唯一性索引的一种之外，还有一点很重要，我们掰开了揉碎了来说。

为了方便演示，我就创建一个简单的表unique_test/create table unique_test(id int,name varchar(30))

添加唯一性约束

alter table unique_test add unique key(id);

插入1行数据

insert into unique_test values(1,'aa');

再插入1行，毫无疑问会抛出错误。

insert into unique_test values(1,'aa');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

我们删除原来的索引，创建一个新的索引，基于列（id,name）

alter table unique_test drop index id;
alter table unique_test add unique key (id,name);

创建新的索引

> insert into unique_test values(1,'aa');
ERROR 1062 (23000): Duplicate entry '1-aa' for key 'id'

可见唯一性约束是生效了，插入不冲突的数据没有任何问题。

insert into unique_test values(1,'bb');

所以这样来看，多个键值列也都能校验出来嘛，我们再建一个列，创建一个复合索引，含有3个列。

> alter table unique_test drop index id

创建一个列created,换个数据类型。

> alter table unique_test add column created datetime;

创建唯一性索引，基于3个列。

> alter table unique_test add unique key(id,name,created);

这个时候模拟一下数据

> insert into unique_test values(1,'aa',null);

这个时候问题就很明显了，竟然校验不出来了。

> select *from unique_test;
+------+------+---------+
| id   | name | created |
+------+------+---------+
|    1 | aa   | NULL    |
|    1 | aa   | NULL    |
|    1 | bb   | NULL    |
+------+------+---------+
3 rows in set (0.00 sec)

这问题在哪儿呢。

我们来看看create table的语句。

> show create table unique_test；
+-------------+-------------------------------------
| Table | Create Table |
+-------------+--------------------------------------
| unique_test | CREATE TABLE `unique_test` (
`created` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------我就把问题点透，就在哪个null的地方上，这个是这个问题的根本，进一步来说，这个是唯一性索引和主键的一个差别，那就是主键约束相比唯一性约束来说，还有一个默认的属性，那就是not null

但是同样都是null的差别，MySQL和Oracle的结果是否相同呢。我们来测试一下。顺便熟悉一下两种数据库的语法风格。

在Oracle里面，代表的含义是不同的，大大不同，可以看看下面的结果来对比一下。

SQL> create table unique_test(id number,name varchar2(30));
Table created.
SQL> alter table unique_test add constraint uq_test unique(id);
Table altered.

SQL> insert into unique_test values(1,'a');
1 row created.

SQL> /
insert into unique_test values(1,'a')
*
ERROR at line 1:
ORA-00001: unique constraint (PDB_MGR.UQ_TEST) violated

SQL> alter table unique_test drop constraint uq_test;
Table altered.

SQL> alter table unique_test add constraint uq_test unique(id,name);
Table altered.

SQL> insert into unique_test values(2,'bb');
1 row created.

SQL> commit;

SQL> alter table unique_test drop constraint uq_test;

SQL> alter table unique_test add created date;

SQL> alter table unique_test add constraint uq_test unique(id,name,created);
Table altered.

SQL> insert into unique_test values(1,'a',null);
insert into unique_test values(1,'a',null)
*
ERROR at line 1:
ORA-00001: unique constraint (PDB_MGR.UQ_TEST) violated

SQL> insert into unique_test values(2,'bb',null);
insert into unique_test values(2,'bb',null)
*
ERROR at line 1:
ORA-00001: unique constraint (PDB_MGR.UQ_TEST) violated