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

167次閱讀

共計 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 行業資訊頻道。

正文完