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

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

   這方面丁奇大師也做了很多詳細的說明，還定製了引數，具體可以參見

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

我們建立一個表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 */;所以一個看起來很簡單的資料庫重啟工作可能帶給我們的會有一些潛在的隱患。