mysql8.0出現(xiàn)自增列值重復(fù)利用問題怎么樣解決

下文主要給大家?guī)?a title="mysql" target="_blank" href="http://www.kemok4.com/mysql/">mysql8.0出現(xiàn)自增列值重復(fù)利用問題怎么樣解決，希望這些內(nèi)容能夠帶給大家實(shí)際用處，這也是我編輯mysql8.0出現(xiàn)自增列值重復(fù)利用問題怎么樣解決這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

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

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

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

InnoDB AUTO_INCREMENT鎖定模式：

   innodb_autoinc_lock_mode 配置參數(shù) 有三種可能的設(shè)置 。對(duì)于“ 傳統(tǒng) ”，“ 連續(xù) ”或 “ 交錯(cuò) ”鎖定模式，設(shè)置分別為0,1或2 。從MySQL 8.0開始，交錯(cuò)鎖定模式（innodb_autoinc_lock_mode=2）是默認(rèn)設(shè)置。在MySQL 8.0之前，連續(xù)鎖定模式是默認(rèn)值（innodb_autoinc_lock_mode=1）。

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

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

--因自增主鍵沒有持久化而出現(xiàn)問題的常見場(chǎng)景：

1. 業(yè)務(wù)將自增主鍵作為業(yè)務(wù)主鍵，同時(shí)，業(yè)務(wù)上又要求主鍵不能重復(fù)。

2. 數(shù)據(jù)會(huì)被歸檔。在歸檔的過程中有可能會(huì)產(chǎn)生主鍵沖突。

所以，強(qiáng)烈建議不要使用自增主鍵作為業(yè)務(wù)主鍵。刨除這兩個(gè)場(chǎng)景，其實(shí)，自增主鍵沒有持久化的問題并不是很大，遠(yuǎn)沒有想象中的”臭名昭著“。

--：最后，給出一個(gè)歸檔場(chǎng)景下的解決方案，

創(chuàng)建一個(gè)存儲(chǔ)過程，根據(jù)table2（歸檔表）自增主鍵的最大值來初始化table1（在線表）。這個(gè)存儲(chǔ)過程可放到init_file參數(shù)指定的文件中，該文件中的SQL會(huì)在數(shù)據(jù)庫啟動(dòng)時(shí)執(zhí)行。

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 ;

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

下面復(fù)現(xiàn)MySQL8.0之前，自增主鍵復(fù)用的情況：

----創(chuàng)建測(cè)試表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自增的下一個(gè)數(shù)字是 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的數(shù)據(jù)，然后再插入一條數(shù)據(jù)

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)

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

---刪除id為4的數(shù)據(jù)，只保留第一行數(shù)據(jù)，然后重啟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后，再次插入一條數(shù)據(jù)：

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，這時(shí)候就是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)

---此時(shí)再插入一條數(shù)據(jù)，會(huì)發(fā)現(xiàn)自增id重復(fù)利用了：

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)

從上面的測(cè)試，我們看到在插入新的數(shù)據(jù)之前AUTO_INCREMENT為2，然后插入了（null,'feng'）， 上面的測(cè)試反映了mysqld重啟后，InnoDB存儲(chǔ)引擎的表自增id可能出現(xiàn)重復(fù)利用的情況。如果存在從庫可能會(huì)導(dǎo)致數(shù)據(jù)庫不一致的情況?。。。。。?！

注意：

  另外，當(dāng)MySQL開啟一個(gè)事務(wù)后，有類INSERT操作，自增值就會(huì)增加；但是當(dāng)事務(wù)回滾后，自增值并不會(huì)減小。也就是說自增值會(huì)有空洞。

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

二、不同的insert插入方式，會(huì)對(duì)有自增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     ---這時(shí)候查看該表的自增值是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   ----這時(shí)候會(huì)發(fā)現(xiàn)自增值變成了8，但是查看該表的數(shù)據(jù)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);   ---此時(shí)，向該表插入一條數(shù)據(jù)，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)
從上面的測(cè)試可以看出，采用null寫法的時(shí)候，自增長值會(huì)多增加一個(gè)值。
2、第二種：使用insert into ...select 方式插入數(shù)據(jù)，如下：
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表的數(shù)據(jù)插入到t1
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)
mysql> show create table t1;   ---然后查看下t1表的自增值，發(fā)現(xiàn)現(xiàn)在是4了，而數(shù)據(jù)只有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方式，自增長也會(huì)多增加一個(gè)值。

注意：

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

究其原因，和insert語句的定位也有關(guān)系，目前有這幾類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’);

這個(gè)和參數(shù)innodb_autoinc_lock_mode有很大的關(guān)系，默認(rèn)參數(shù)值為1。innodb_autoinc_lock_mode這個(gè)參數(shù)控制著在向有auto_increment列的表插入數(shù)據(jù)時(shí)，相關(guān)鎖的行為，有三個(gè)取值：

0：這個(gè)表示tradition（傳統(tǒng)）

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

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

1：這個(gè)表示consecutive（連續(xù)）

   這一模式下對(duì)simple insert做了優(yōu)化，由于simple insert一次性插入值的個(gè)數(shù)可以立馬得到確定，所以MySQL可以一次生成幾個(gè)連續(xù)的值，用于這個(gè)insert語句；總的來說這個(gè)對(duì)復(fù)制也是安全的（它保證了基于語句復(fù)制的安全）。由于現(xiàn)在MySQL已經(jīng)推薦把二進(jìn)制的格式設(shè)置成ROW格式，所以沒有復(fù)制安全問題了。

這一模式也是MySQL的默認(rèn)模式，這個(gè)模式的好處是auto_inc鎖不要一直保持到語句的結(jié)束，只要語句得到了相應(yīng)的值后就可以提前釋放鎖。

2：這個(gè)表示interleaved（交錯(cuò)）

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

注意：在MySQL8.0版本中已經(jīng)將innodb_autoinc_lock_mode該參數(shù)的默認(rèn)值改為2?。。。。?/p>

對(duì)于以上關(guān)于mysql8.0出現(xiàn)自增列值重復(fù)利用問題怎么樣解決，大家是不是覺得非常有幫助。如果需要了解更多內(nèi)容，請(qǐng)繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會(huì)喜歡上這些內(nèi)容的。