MySQL和Oracle中的唯一性索引的差别是怎样的

这篇文章是关于MySQL和Oracle的唯一索引的区别。边肖觉得很实用，所以分享给大家学习。希望你看完这篇文章能有所收获。让我们和边肖一起看看。

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

表中有一个唯一的索引，它包含三列。这个唯一索引的意义在于，可以通过这三列定位特定行的数据。但是在实践中发现，这个独特的指数中还是有可能被大家忽略的地方。

我们先来看看数据。

CREATE TABLE `test_base_data `(

服务器时间` datetime默认空注释` time ‘，

` appkey` varchar(64) DEFAULT NULL，

.

`时区` varchar (50)默认空注释’时区’，

UNIQUE KEY ` server time _ appkey _ timezone `(` server time `、` app KEY `、` time zone `)，

KEY ` idx _ CCB _ r _ b _ d _ AK _ time `(` server time `，` appkey `)

)ENGINE=Innodb DEFAULT CHARSET=ut F8

表中的数据量约为300万。

从test_base_data中选择count(*)；

–

|计数(*) |

–

| 3818630 |

–

我在分析一个问题的时候发现，根据目前的情况来看，主键和唯一索引好像有点区别(当然，问题本身回想起来也很清楚)。

所以我尝试删除这个唯一的索引并创建一个主键，但是这个操作抛出了一个数据冲突的错误。

alter table test_base_data添加主键“server time _ appkey _ timezone”(“server time”、“appkey”、“time zone ”);

错误1062 (23000):键“PRIMARY”的重复条目“2017-05-09 13:15:00-1461048746259-”

根据appkey 1461048746259过滤数据，得到一个基本情况如下：

从ccb_realtime_base_data limit 5中选择servertime、appkey、时区；

– – –

| servertime | appkey |时区|

– – –

| 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 |

– – –

单纯这样看，看不出有什么不对，但是当我有计数得到重复数据的时候，真的让我惊呆了。

从ccb_realtime_base_data中选择count(1)，其中server time=’ 2017-05-09 13:15:00 ‘和appkey=’ 1461048746259

–

|计数(1) |

–

r/>|      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

以上就是MySQL和Oracle中的唯一性索引的差别是怎样的，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。