MySQL和Oracle中唯一性索引的差别(r12笔记第83天)

今天在修复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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-06-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏性能与架构

Mysql group by实现方式(一) - 临时表

当MySQL Query Optimizer无法找到可以利用的合适索引时,就不得不先读取需要的数据,然后通过临时表来完成GROUP BY操作 例如 EXPLAI...

29760
来自专栏java达人

oracle、mysql 分页查询比较

1、 Oracle的分页查询语句 分页查询格式: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * ...

29280
来自专栏游戏杂谈

Node.js调用mysql的存储过程

例子仅在windows下测试通过,没有放在linux下测试。如有问题,可以电邮给我~

19110
来自专栏杨建荣的学习笔记

MySQL中一个文档疏漏的分析测试

最近看到Percona的工程师Agustín写了一篇博客,是关于MySQL触发器和可更新视图的一个观点,具体链接可以参考 https://www.percona...

34190
来自专栏Pythonista

mysql索引提高查询速度

  在web开发中,业务模版,业务逻辑(包括缓存、连接池)和数据库这三个部分,数据库在其中负责执行SQL查询并返回查询结果,是影响网站速度最重要的性能瓶颈。本文...

15230
来自专栏Clive的技术分享

MySQL ORDER BY主键id加LIMIT限制走错索引

65030
来自专栏Netkiller

数据库记录安全解决方案

数据库记录安全解决方案 http://netkiller.github.io/journal/mysql.security.html 摘要 数据库记录防删除,放...

41960
来自专栏一个爱吃西瓜的程序员

学习SQL【5】-数据更新

数据的更新处理大体可以分为插入(INSERT)、删除(DELETE)和更新(UPDATE)三类,此外,还会给大家介绍数据库中用来管理数据更新的重要概念—事务。 ...

37170
来自专栏乐沙弥的世界

SQL与mongoDB对比及映射

mongoDB是最接近与关系型数据库的开源NoSQL数据库,几乎绝大多数常用的命令或语句在SQL中都可以找到相应的对应或映射。本文主要与MySQL做了些参照,供...

17120
来自专栏Netkiller

数据库记录安全解决方案

目录 1. 什么是防删除,防撰改 2. 为什么要做防删除,防撰改限制 3. 何时做防删除,防撰改限制 4. 在哪里做防删除,防撰改限制 5. 谁去做防删除,防撰...

30550

扫码关注云+社区

领取腾讯云代金券