AUTO_INCREMENT超過表中該字段的最大值
更新時間 2023-07-04 19:46:37
最近更新時間: 2023-07-04 19:46:37
分享(xiang)文章
本文針對AUTO_INCREMENT超過表中該字段的(de)最大值(zhi)時(shi)產生(sheng)的(de)問題及應對方(fang)案進行了說明。
表的自動新增AUTOINCREMENT超過數據中該字段的最大值
在數(shu)據(ju)表(biao)中(zhong)會發現AUTO_INCREMENT的值不(bu)等于表(biao)中(zhong)字段最(zui)大值+1，可能原因有以(yi)下幾(ji)種(zhong)：
1.步長(chang)不(bu)為1，則AUTO_INCREMENT=最大值+步長(chang)。
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> select * from tianyiy;
+-----+
| uid |
+-----+
| 5 |
| 15 |
| 25 |
+-----+
3 rows in set (0.00 sec)
mysql> show create table tianyiy;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tianyiy | CREATE TABLE `tianyiy` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.直接(jie)修改表的AUTO_INCREMENT，會(hui)導致AUTO_INCREMENT變化。
mysql> select * from tianyiyun;
+-----+------+
| uid | user |
+-----+------+
| -30 | k |
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
| 30 | j |
| 99 | i |
+-----+------+
11 rows in set (0.00 sec)
mysql> show create table tianyiyun;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tianyiyun | CREATE TABLE `tianyiyun` (
`uid` mediumint(9) NOT NULL AUTO_INCREMENT,
`user` char(10) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table tianyiyun AUTO_INCREMENT=99;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tianyiyun;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tianyiyun | CREATE TABLE `tianyiyun` (
`uid` mediumint(9) NOT NULL AUTO_INCREMENT,
`user` char(10) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.未(wei)提交(jiao)的事務(wu)(wu)或(huo)回(hui)(hui)滾的事務(wu)(wu)，會(hui)導致AUTO_INCREMENT增長，但回(hui)(hui)滾后不(bu)會(hui)下降。
mysql> show create table tiayiyun_test;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from tiayiyun_test;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+-----+
6 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tiayiyun_test values (0),(0),(0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tiayiyun_test;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 15 |
| 25 |
| 35 |
+-----+
9 rows in set (0.00 sec)
mysql> show create table tiayiyun_test;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from tiayiyun_test;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+-----+
6 rows in set (0.00 sec)
mysql> show create table tiayiyun_test;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.數據插入后，AUTO_INCREMENT變化，然后刪除對應(ying)的數據行(xing)，AUTO_INCREMENT不會下降。
mysql> show create table tiayiyun_test;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from tiayiyun_test;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+-----+
6 rows in set (0.00 sec)
mysql> insert into tiayiyun_test values (0),(0),(0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tiayiyun_test;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 45 |
| 55 |
| 65 |
+-----+
9 rows in set (0.00 sec)
mysql> show create table tiayiyun_test;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> delete from tiayiyun_test where uid>3;
Query OK, 6 rows affected (0.00 sec)
mysql> select * from tiayiyun_test;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
3 rows in set (0.00 sec)
mysql> show create table tiayiyun_test;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiayiyun_test | CREATE TABLE `tiayiyun_test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
