Shulou(Shulou.com)06/01 Report--

If you need to archive the data in one MySQL to another MySQL history library on a regular basis, you may find that there is a problem of duplicate values, resulting in the failure of data import. This problem is actually related to the duplicate values of self-incremented columns. Let's take a brief look.

Master Dingqi has also made a lot of detailed instructions and customized parameters in this respect. For more information, please see http://www.csdn.net/article/2015-01-16/2823591

Let's take a look at this problem and make a simple summary.

We create a table T1 and specify the storage engine as InnoDB

Use test

[test] > drop table if exists T1

Query OK, 0 rows affected, 1 warning (0.01 sec)

> create table T1 (id int auto_increment, an int, primary key (id)) engine=innodb

Query OK, 0 rows affected (0.02 sec) and then insert 3 pieces of data, the first one specifies id as 1, and the last two id values increase by themselves.

Insert into T1 values (1Pol 2)

Insert into T1 values (null,2)

Insert into T1 values (null,2); the distribution of data is as follows:

[test] > select * from T1

+-+ +

| | id | a |

+-+ +

| | 1 | 2 |

| | 2 | 2 |

| | 3 | 2 |

At this point, our data initialization is complete.

At this time, use show create table to view, define the value of the self-incrementing column in the information as 4, that is, insert another record with an id value of 4. 5.

> show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

1 row in set (0.00 sec) We then clean up the data with id of 2 and 3.

Delete from t1 where id=2

Delete from t1 where id=3

I would like to complain that MySQL should be able to support the following statement.

[test] > delete from T1 where id

Query OK, 2 rows affected (0.00 sec)

Of course, we moved on to see what happened after the data was deleted, leaving only one piece of data with an id of 1.

> select * from T1

+-+ +

| | id | a |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0. 00 sec) next if we continue to insert a record, then the id will be 4. 0.

But we don't do that, we restart MySQL.

Service mysql stop

Service mysql start then inserts a record, where the id value is calculated from 2 instead of 4. 5.

Insert into T1 values (null,2)

[test] > select * from T1

+-+ +

| | id | a |

+-+ +

| | 1 | 2 |

| | 2 | 2 |

+-+ +

2 rows in set (0.00 sec) if you look at the table definition information at this time, you will find that the self-increment column is currently 3.

> show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

What's the reason for this? if you try MyISAM, you won't have this kind of problem, but for InnoDB, its self-incrementing implementation must be out of memory after reboot, it is calculated according to max (id) + 1.

This situation exists not only in MySQL 5.5, but also in MySQL 5.7.

And whether such problems will occur in data migration, we also need to pay attention to.

For example, we use mysqldump to export data and then import it into another environment.

Export data

Mysqldump test T1 > t1.sql

The exported sql text is as follows, and you can see how the id value is specified, not empty.

LOCK TABLES `t1` WRITE

/ *! 40000 ALTER TABLE `t1` DISABLE KEYS * /

INSERT INTO `t1` VALUES (1pm 2), (2pm 2)

/ *! 40000 ALTER TABLE `t1` ENABLE KEYS * /

UNLOCK TABLES

/ *! 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /; so a seemingly simple database restart may bring us some potential dangers.

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.