这篇文章是关于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中的唯一性索引的差别是怎样的,小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。
内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/95842.html