MySQL-对自动新增AUTO_INCREMENT的值进行修改

本文解释如何对AUTO_INCREMENT的值进行修改操作。

对AUTO_INCREMENT的值进行修改

修改方式如下：

1.如果AUTO_INCREMENT的值大于数据库的最大值的时候，可以在这个范围内取任何一个值修改为更大的值。

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=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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    |
| 108 | z    |
| 109 | q    |
+-----+------+
13 rows in set (0.00 sec)
mysql> alter table tianyiyun AUTO_INCREMENT=200;
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=200 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.如果AUTO_INCREMENT的值大于表中的值的时候，修好指定的值大于数据最大值，修改会成功，否则会默认最大值+1。

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    |
| 108 | z    |
| 109 | q    |
+-----+------+
13 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=200 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table tianyiyun AUTO_INCREMENT=150;
Query OK, 0 rows affected (0.01 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=150 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table tianyiyun AUTO_INCREMENT=50;
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=110 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> delete from tianyiyun where uid=99;
Query OK, 1 row affected (0.00 sec)
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    |
| 108 | z    |
| 109 | q    |
+-----+------+
12 rows in set (0.00 sec)
mysql> alter table tianyiyun AUTO_INCREMENT=50;
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=110 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.如果AUTO_INCREMENT的值修改为负数是无法修改的。

mysql> alter table tianyiyun AUTO_INCREMENT=-1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1