MySQL自增列的重復值問題(r12筆記第25天)

   如果需要把一臺MySQL中的數據定期歸檔到另外一臺MySQL歷史庫中，那么很可能會發現會有重復值的問題，導致數據導入會失敗，而這個問題其實是和自增列的重復值有關，我們來簡單看看。

   這方面丁奇大師也做了很多詳細的說明，還定制了參數，具體可以參見 http://www.csdn.net/article/2015-01-16/2823591

    我們來看看這個問題，由此做一個簡單的總結。

我們創建一個表t1，指定存儲引擎為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, a int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)然后插入3條數據，第一條指定id為1，后面兩條id值自增。

insert into t1 values (1,2);
insert into t1 values (null,2);
insert into t1 values (null,2);數據的分布情況如下：
[test]> select *from t1;               
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
+----+------+到此為止，我們的數據初始化工作就完成了。

這個時候使用show create table查看，定義信息中自增列的值為4，即再插入一條記錄，id值為4.

> 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)我們接著清理id為2和3的數據。

delete from t1 where id=2;
delete from t1 where id=3;

在此吐槽一句，MySQL竟然能夠支持下面這樣的語句，我都方了。

[test]> delete from t1 where id;
Query OK, 2 rows affected (0.00 sec)

當然我們繼續往下做，查看刪除數據之后的情況，只保留了一條id為1的數據。

> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
+----+------+
1 row in set (0.00 sec)接下來我們如果繼續插入一條記錄，那么id就會是4.

但是我們不這么做，我們重啟MySQL。

service mysql stop
service mysql start然后插入一條記錄,這個時候id值是從2開始計算了，而不是4.

insert into t1 values (null,2);
[test]> select *from t1;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)這個時候如果查看表定義信息，就會發現自增列目前是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)

這是什么原因呢，如果你試試MyISAM，就不會出現這類問題，而對于InnoDB來說，它的自增列的實現在重啟之后內存中肯定是沒有了，它是根據max(id)+1的方式來計算的。

這個情況不光是在MySQL 5.5存在，在MySQL 5.7也依舊存在。

而這類問題是否在數據遷移中會出現呢，我們也需要注意一下。

比如我們使用mysqldump導出數據，然后導入到另外一個環境。

導出數據

mysqldump  test t1 > t1.sql 
導出的sql文本如下，可以看到里面是指定id值的方式，而非空。
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,2),(2,2);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;所以一個看起來很簡單的數據庫重啟工作可能帶給我們的會有一些潛在的隱患。