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

时间: 2024-09-21 15:13:52

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

MySQL和Oracle中的隐式转换

今天在处理一个问题的时候,需要根据其他部门提供的sql语句对一个表中的数据进行了筛查. 语句类似下面的形式 > SELECT MAX_LEVEL,LOGOUT_TIME,CURRENT_DATE AS NOWTIME,cn_master FROM t_test_october_back_a WHERE ID in ( 100, 200, 300, 400, 500) ; +-----------+---------------+------------+-----------+ | MAX_LE

从Java的类型转换看MySQL和Oracle中的隐式转换(二)

说起数据类型转换,在开发中如此,在数据库中也是如此,之前简单对比过MySQL和Oracle的数据类型转换情况,可以参见MySQL和Oracle中的隐式转换 http://blog.itpub.net/23718752/viewspace-1787973/ 不过当时写完之后,有个读者随口问了一句为什么,为什么呢?似乎自己还是一知半解,说是规则,无规矩不成方圆,倒也无可非议,不过我觉得还是要再看看,看看还能有哪些收获,接下来的内容我就不能保证正确性了,希望大家明辨,也希望提出意见,毕竟就是希望把问题

由一条create语句的问题对比mysql和oracle中的date差别

今天开发的同事提交过来一个sql变更,在部署的时候发现了一个问题. 语句是一个简单的create语句 CREATE TABLE `test_user` (   `openid` varchar(64) NOT NULL,   `amount` varchar(11) DEFAULT 0,   `create_time` datetime DEFAULT CURRENT_TIMESTAMP,   `update_time` datetime DEFAULT CURRENT_TIMESTAMP,  

简单对比MySQL和Oracle中的一个sql解析细节

SQL的语法解析器是一个很强大的内置工具集,里面会涉及到很多的编译原理的相关知识,语法分析,词法分析..一大堆看起来很理论的东东,不过看起来枯燥之余,它们的价值也更加明显. 借用一下网络中的原话:如果我们考究一下历史,就会发现很多被称为程序设计大师的人都是编译领域的高手.写出第一个微型机上运行的Basic语言的比尔盖茨,设计出Delphi的Borland的"世界上最厉害的程序员", Sun的JAVA之父, 贝尔实验室的C++之父 起点提得有些高了,今天和大家分享的案例是一个很简单的sq

MySQL和Oracle中的delete,truncate对比

在MySQL和Oracle中的delete,truncate还是存在着一些差别,明白了这些差别可能对于处理问题,理解问题会有一些帮助. 我们来简单通过一些测试来说明.我们创建两个表test_del,test_tru来对比delete,truncate的操作.我们有一个临时表t_fund_info大概有几百万的数据量. 创建test_del > create table test_del select *from t_fund_info; Query OK, 1998067 rows affect

MySQL和Oracle中的半连接测试总结(一)

SQL中的半连接在MySQL和Oracle还是存在一些差距,从测试的情况来看,Oracle的处理要更加全面. 首先我们来看看在MySQL中怎么测试,对于MySQL方面的测试也参考了不少海翔兄的博客文章,自己也完整的按照他的测试思路练习了一遍. 首先创建下面的表: create table users( userid int(11) unsigned not null, user_name varchar(64) default null, primary key(userid) )engine=

Oracle中如何管理索引组织表

索引组织表(IOT)有一种类B树的存储组织方法.普通的堆组织表是以一种无序的集合存储.而IOT中的数据是按主键有序的存储在B树索引结构中.与一般B树索引不同的的是,在IOT中每个叶结点即有每行的主键列值,又有那些非主键列值. 在IOT所对应的B树结构中,每个索引项包括<主键列值,非主键列值>而不是ROWID,对于普通堆组织表,oracle会有对应的索引与之对应,且分开存储.换句话说,IOT即是索引,又是实际的数据. 索引组织表(IOT)不仅可以存储数据,还可以存储为表建立的索引.索引组织表的数

MySQL和Oracle的添加字段的处理差别

昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道Oracle中这个操作就比较纠结了,因为是按照追加的方式来处理的.没法在已有的字段1,字段2中间添加一个字段3.但是MySQL却可以,这个方面MySQL看起来要灵活的多,这个是什么原因呢,他们在设计上有什么差别呢. MySQL中对每个表存在一个定义文件,即frm文件,我们来取出一个表,看看能不能简单解析一下. 比如一个表字段的内容如下: > desc zd_warshrine_prostate;

Oracle中常数复合索引的应用案例

从一个客户的真实优化案例引申的问题. 客户的一个数据库需要进行优化,不过由于程序开发方没有介入,因此这次优化无法对SQL进行修改. 仅对数据库级的调整一般来说收效不大,不过发现客户数据库中个别的SQL存在性能问题,且这个性能问题已经影响到整个数据库.如果可以将这个SQL优化,那么可以解决目前数据库的性能问题.幸运的是,这个问题可以通过添加索引来进行优化. 模拟问题SQL如下: SQL> select * from v$version; BANNER -----------------------