MySQL自增列的重復值問題

2024-07-24 12:31:23

字體：大中小

供稿：網(wǎng)友

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

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

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

我們創(chuàng)建一個表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條數(shù)據(jù)，第一條指定id為1，后面兩條id值自增。

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

這個時候使用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的數(shù)據(jù)。

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)

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

> select * from t1;
+----+------+
| id | a    |
+----+------+
| 1 |    2 |
+----+------+
1 row in set (0.00 sec)接下來我們?nèi)绻^續(xù)插入一條記錄，那么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)這個時候如果查看表定義信息，就會發(fā)現(xiàn)自增列目前是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，就不會出現(xiàn)這類問題，而對于InnoDB來說，它的自增列的實現(xiàn)在重啟之后內(nèi)存中肯定是沒有了，它是根據(jù)max(id)+1的方式來計算的。

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

而這類問題是否在數(shù)據(jù)遷移中會出現(xiàn)呢，我們也需要注意一下。

比如我們使用mysqldump導出數(shù)據(jù)，然后導入到另外一個環(huán)境。

導出數(shù)據(jù)

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 */;所以一個看起來很簡單的數(shù)據(jù)庫重啟工作可能帶給我們的會有一些潛在的隱患。

（編輯：武林網(wǎng)）

上一篇：破除本地 mysql 用戶名和密碼

下一篇：mysql在線創(chuàng)立半同步復制的從庫