對自動新增AUTO_INCREMENT的值進行修改
更新時間 2023-07-04 19:48:15
最近更新時間: 2023-07-04 19:48:15
分享文章
本文解釋如何對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
