MySQL和Oracle中的唯一性索引的差別是怎樣的

本篇文章給大家分享的是有關MySQL和Oracle中的唯一性索引的差別是怎樣的，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

   今天在修復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中的唯一性索引的差別是怎樣的，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。