Shulou(Shulou.com)05/31 Report--

This article to share with you is about MySQL and Oracle in the unique index of what is the difference, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

Today, while fixing MySQL data, I found a problem that looked "strange".

There is a unique index in a table, this index contains 3 columns, the meaning of this unique index is that through these 3 columns can locate a specific row of data, but in practice it is found that there is still a place in this unique index that may be ignored by everyone.

Let's look at the data first.

CREATE TABLE `test_base_data` (

`servertime` datetime DEFAULT NULL COMMENT 'time',

`appkey` varchar(64) DEFAULT NULL,

...

`timezone` varchar(50) DEFAULT NULL COMMENT 'timezone',

UNIQUE KEY `servertime_appkey_timezone` (`servertime`,`appkey`,`timezone`),

KEY `idx_ccb_r_b_d_ak_time` (`servertime`,`appkey`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

The data in the table is about 3 million

> select count(*)from test_base_data;

+----------+

| count(*) |

+----------+

| 3818630 |

+----------+

When I analyze a problem, I find that according to the current situation, there seems to be a slight difference between primary key and unique index (of course, in retrospect, the problem itself is very clear).

So I tried to delete the unique index and create a primary key instead, but this operation threw a data collision error.

> 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'

The data is filtered according to appkey 1461048746259, and a basic situation is obtained as follows:

> 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 |

+---------------------+---------------+----------+

I didn't see anything wrong with that, but I was shocked when I had count to get duplicate data.

> select count(1) from ccb_realtime_base_data where servertime ='2017-05-09 13:15:00' and appkey='1461048746259';

+----------+

| count(1) |

+----------+

| 709 |

+----------+

There were more than 700 duplicate records in this table.

In this case, the missing data in the table is a big problem, but why can't the unique index find it?

On this point, Oracle and MySQL's position is the same, that is, the difference between the primary key and the unique index, out of the root of the primary key red seedling positive, the primary key is a unique index, there is also a very important point, we broke apart the broken.

To illustrate, I'll create a simple table unique_test\create table unique_test(id int,name varchar(30))

Add uniqueness constraints

alter table unique_test add unique key(id);

Insert 1 line of data

insert into unique_test values(1,'aa');

Inserting one more line would undoubtedly throw an error.

insert into unique_test values(1,'aa');

ERROR 1062 (23000): Duplicate entry '1' for key 'id'

We delete the old index and create a new one, based on the column (id,name)

alter table unique_test drop index id;

alter table unique_test add unique key (id,name);

create a new index

> insert into unique_test values(1,'aa');

ERROR 1062 (23000): Duplicate entry '1-aa' for key 'id'

The visibility uniqueness constraint is in effect and there is no problem inserting non-conflicting data.

insert into unique_test values(1,'bb');

So in this way, multiple key columns can also be checked out, we build another column, create a composite index, containing 3 columns.

> alter table unique_test drop index id

Create a column created with a different data type.

> alter table unique_test add column created datetime;

Create a unique index, based on 3 columns.

> alter table unique_test add unique key(id,name,created);

At this point simulate the data

> insert into unique_test values(1,'aa',null);

At this time, the problem was very obvious. It could not be verified.

> select *from unique_test;

+------+------+---------+

| id | name | created |

+------+------+---------+

| 1 | aa | NULL |

| 1 | aa | NULL |

| 1 | bb | NULL |

+------+------+---------+

3 rows in set (0.00 sec)

Where is the problem?

Let's look at the create table statement.

> show create table unique_test;

+-------------+-------------------------------------

| Table | Create Table |

+-------------+--------------------------------------

| unique_test | CREATE TABLE `unique_test` (

`created` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+---------------------------------------

However, the difference between MySQL and Oracle is also null. Let's test it. By the way, familiarize yourself with the syntax styles of the two databases.

In Oracle, the meaning of representation is different, very different, you can see the following results to compare.

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

The above is the difference between MySQL and Oracle's unique index, Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.