mysql8.0出現自增列值重復利用問題怎么樣解決

下文主要給大家帶來mysql8.0出現自增列值重復利用問題怎么樣解決，希望這些內容能夠帶給大家實際用處，這也是我編輯mysql8.0出現自增列值重復利用問題怎么樣解決這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

    這在很多場景下可能導致問題，包括但不限于：主備切換、歷史數據遷移等場景。在bug#199下面一大堆的回復里，可以看到大量的同行抱怨。如，假設t1有個歷史表t1_history用來存t1表的歷史數據，那么mysqld重啟前，ti_history表中可能已經有了（2,2）這條數據，而重啟后我們又插入了（2，2），當新插入的(2,2)遷移到歷史表時，會違反主鍵約束。這類問題是否在數據遷移中會出現呢，我們也需要注意一下。比如我們使用mysqldump導出數據，然后導入到另外一個環境。mysqldump導出數據里面是指定了自增長值的方式，而非空。

建表時可以指定AUTO_INCREMENT值，不指定時默認為1，這個值表示當前自增列的起始值大小，如果新插入的數據沒有指定自增列的值，那么自增列的值即為這個起始值。對于InnoDB表，這個值沒有持久到文件中。而是存在內存中（dict_table_struct.autoinc）。那么又問，既然這個值沒有持久下來，為什么我們每次插入新的值后，show create table t1看到AUTO_INCREMENT值是跟隨變化的。其實show create table t1是直接從dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。

   知道了AUTO_INCREMENT是實時存儲內存中的。那么，mysqld重啟后，從哪里得到AUTO_INCREMENT呢？內存值肯定是丟失了，實際上MySQL采用執行類似select max(id)+1 from t1;方法來得到AUTO_INCREMENT，而這種方法就是造成自增id重復的原因。

InnoDB AUTO_INCREMENT鎖定模式：

   innodb_autoinc_lock_mode 配置參數 有三種可能的設置 。對于“ 傳統 ”，“ 連續 ”或 “ 交錯 ”鎖定模式，設置分別為0,1或2 。從MySQL 8.0開始，交錯鎖定模式（innodb_autoinc_lock_mode=2）是默認設置。在MySQL 8.0之前，連續鎖定模式是默認值（innodb_autoinc_lock_mode=1）。

《在MySQL8.0的解決思路：》

   將自增主鍵的計數器持久化到redo log中。每次計數器發生改變，都會將其寫入到redo log中。如果數據庫發生重啟，InnoDB會根據redo log中的計數器信息來初始化其內存值。為了盡量減小對系統性能的影響，計數器寫入到redo log中，并不會馬上刷新。

--因自增主鍵沒有持久化而出現問題的常見場景：

1. 業務將自增主鍵作為業務主鍵，同時，業務上又要求主鍵不能重復。

2. 數據會被歸檔。在歸檔的過程中有可能會產生主鍵沖突。

所以，強烈建議不要使用自增主鍵作為業務主鍵。刨除這兩個場景，其實，自增主鍵沒有持久化的問題并不是很大，遠沒有想象中的”臭名昭著“。

--：最后，給出一個歸檔場景下的解決方案，

創建一個存儲過程，根據table2（歸檔表）自增主鍵的最大值來初始化table1（在線表）。這個存儲過程可放到init_file參數指定的文件中，該文件中的SQL會在數據庫啟動時執行。

DELIMITER ;;

CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))

BEGIN

set @qry = concat('SELECT @max1 := (`id` + 1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');

    prepare stmt from @qry;

execute stmt;

deallocate prepare stmt;

    set @qry = concat('SELECT @max2 := (`id` + 1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');

    prepare stmt from @qry;

execute stmt;

deallocate prepare stmt;

IF @max1 < @max2 THEN

    set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;

SELECT 'updated' as `status`;

else

SELECT 'no update needed' as `status`;

END IF;

END ;;

DELIMITER ;

###################################################################

下面復現MySQL8.0之前，自增主鍵復用的情況：

----創建測試表emp：

MySQL [test]> create table emp (id int auto_increment,name varchar(10),primary key (id));
MySQL [test]> insert into emp values (1,'zhang');
MySQL [test]> insert into emp values (null,'liu');
MySQL [test]> insert into emp values (null,'huang');
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | liu   |
|  3 | huang |
+----+-------+
3 rows in set (0.00 sec)

---從下面的信息可以看出，emp表id自增的下一個數字是 4

MySQL [test]> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

---刪除id=2和3的數據，然后再插入一條數據

MySQL [test]> delete from emp where id=2 or id=3;
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
+----+-------+
1 row in set (0.01 sec)
MySQL [test]> insert into emp values (null,'hhhh');
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  4 | hhhh  |
+----+-------+
2 rows in set (0.00 sec)

（從上面的結果可以看出，如果不重啟數據庫的情況下，雖然把前面的數據刪除了，但是在此插入數據 它的自增id還是和沒刪除數據之前的順序遞增。）

---刪除id為4的數據，只保留第一行數據，然后重啟MySQL如下：

MySQL [test]> delete from emp where id=4;
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
+----+-------+
1 row in set (0.00 sec)

----重啟MySQL后，再次插入一條數據：

MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
+----+-------+
1 row in set (0.00 sec)
MySQL [test]> show create table emp\G    ---（重啟完MySQL在此查看該表的自增id，這時候就是2了）
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

---此時再插入一條數據，會發現自增id重復利用了：

MySQL [test]> insert into emp values (null,'feng');
MySQL [test]> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | feng  |
+----+-------+
2 rows in set (0.00 sec)

從上面的測試，我們看到在插入新的數據之前AUTO_INCREMENT為2，然后插入了（null,'feng'）， 上面的測試反映了mysqld重啟后，InnoDB存儲引擎的表自增id可能出現重復利用的情況。如果存在從庫可能會導致數據庫不一致的情況?。。。。。?！

注意：

  另外，當MySQL開啟一個事務后，有類INSERT操作，自增值就會增加；但是當事務回滾后，自增值并不會減小。也就是說自增值會有空洞。

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

二、不同的insert插入方式，會對有自增id有不同影響，如下：

1、第一種：帶有 null 值的寫法

mysql> create table emp(id int auto_increment, a int, primary key (id)) engine=innodb;
mysql> insert into emp values (1,2),(2,2),(3,2);
MySQL [test]> show create table emp\G     ---這時候查看該表的自增值是4
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)     
MySQL [test]>  insert into emp values (4,2),(null,2),(null,2);   ---使用null的方式插入值
MySQL [test]> show create table emp\G   ----這時候會發現自增值變成了8，但是查看該表的數據id列最大是6，
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MySQL [test]> select * from emp;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
|  4 |    2 |
|  5 |    2 |
|  6 |    2 |
+----+------+
6 rows in set (0.01 sec)
MySQL [test]> insert into emp values (null,3);   ---此時，向該表插入一條數據，id的值就變成了8
MySQL [test]>  select * from emp;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
|  4 |    2 |
|  5 |    2 |
|  6 |    2 |
|  8 |    3 |
+----+------+
7 rows in set (0.00 sec)
從上面的測試可以看出，采用null寫法的時候，自增長值會多增加一個值。
2、第二種：使用insert into ...select 方式插入數據，如下：
mysql> create table t1(id int auto_increment primary key,name varchar(255));
mysql> create table t2(name varchar(255))engine=innodb;
mysql> insert into t2 values('aa'),('bb');
mysql> insert into t1(name) select *from t2;   ---將t2表的數據插入到t1
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)
mysql> show create table t1;   ---然后查看下t1表的自增值，發現現在是4了，而數據只有2條
| Table | Create Table                                                                                           
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

這也說明了，采用insert .. select方式，自增長也會多增加一個值。

注意：

   我們可以看到第一種帶NULL值的寫法，自增長值會多增加一個值；第二種insert .. select，自增長也會多增加一個值。這個會帶來什么問題呢？你會發現從庫自增長值卻是正常的（當復制格式為ROW時），這個時候其實也就是主從數據不一致了，但影響不大，除非出現記錄ID大于自增長ID，那樣插入數據重復會報錯。

究其原因，和insert語句的定位也有關系，目前有這幾類insert語句。

1、simple insert，如insert into t(name) values(‘test’)

2、bulk insert，如load data | insert into … select …. from ….

3、mixed insert，如insert into t(id,name) values(1,’a’),(null,’b’),(5,’c’);

這個和參數innodb_autoinc_lock_mode有很大的關系，默認參數值為1。innodb_autoinc_lock_mode這個參數控制著在向有auto_increment列的表插入數據時，相關鎖的行為，有三個取值：

0：這個表示tradition（傳統）

   它提供了一個向后兼容的能力，在這一模式下，所有的insert語句(“insert like”) 都要在語句開始的時候得到一個表級的auto_inc鎖，在語句結束的時候才釋放這把鎖。注意呀，這里說的是語句級而不是事務級的，一個事務可能包涵有一個或多個語句。

   它能保證值分配的可預見性，與連續性，可重復性，這個也就保證了insert語句在復制到slave的時候還能生成和master那邊一樣的值（它保證了基于語句復制的安全）。由于在這種模式下auto_inc鎖一直要保持到語句的結束，所以這個就影響到了并發的插入。

1：這個表示consecutive（連續）

   這一模式下對simple insert做了優化，由于simple insert一次性插入值的個數可以立馬得到確定，所以MySQL可以一次生成幾個連續的值，用于這個insert語句；總的來說這個對復制也是安全的（它保證了基于語句復制的安全）。由于現在MySQL已經推薦把二進制的格式設置成ROW格式，所以沒有復制安全問題了。

這一模式也是MySQL的默認模式，這個模式的好處是auto_inc鎖不要一直保持到語句的結束，只要語句得到了相應的值后就可以提前釋放鎖。

2：這個表示interleaved（交錯）

   由于這個模式下已經沒有了auto_inc鎖，所以這個模式下的性能是最好的；但是它也有一個問題，就是對于同一個語句來說它所得到的auto_incremant值可能不是連續的。

注意：在MySQL8.0版本中已經將innodb_autoinc_lock_mode該參數的默認值改為2?。。。?！

對于以上關于mysql8.0出現自增列值重復利用問題怎么樣解決，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。