共计 4253 个字符，预计需要花费 11 分钟才能阅读完成。
本篇文章给大家分享的是有关 MySQL 和 Oracle 中的唯一性索引的差别是怎样的，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。
今天在修复 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
以上就是 MySQL 和 Oracle 中的唯一性索引的差别是怎样的，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。
