MySQL數(shù)據(jù)表設(shè)計之自動增長的實現(xiàn)

更新時間：2025年03月12日 10:41:45 作者：堅持就完事了

本文主要介紹了MySQL數(shù)據(jù)表設(shè)計之自動增長的實現(xiàn),包括創(chuàng)建數(shù)據(jù)表時設(shè)置自動增長和修改數(shù)據(jù)表時添加自動增長,具有一定的參考價值,感興趣的可以了解一下

在實際開發(fā)中，有時需要為數(shù)據(jù)表中添加的新紀錄自動生成主鍵值。例如在員工數(shù)據(jù)表中添加員工信息時，如果手動填寫員工工號，需要在添加員工前查詢工號是否被其他員工占用，由于先查詢后添加需要一段時間，有可能會出現(xiàn)并發(fā)操作時工號被其他人搶占的問題，此時可以為員工工號字段設(shè)置自動增長。設(shè)置自動增長后，如果往該字段插入值時，MySQL會自動生成唯一的自動增長值。

通過給字段設(shè)置AUTO_INCREMENT即可實現(xiàn)自動增長。

設(shè)置自動增長的方式有兩種，分別為創(chuàng)建數(shù)據(jù)表時設(shè)置自動增長和修改數(shù)據(jù)表時添加自動增長。

1.創(chuàng)建數(shù)據(jù)表時設(shè)置自動增長

CREATE TABLE 表名 (
字段名 數(shù)據(jù)類型 約束 AUTO_INCREMENT,
...
);

2.修改數(shù)據(jù)表時添加自動增長

# 語法1 MODIFY子句
ALTER TABLE 表名 MODIFY 字段名 數(shù)據(jù)類型 AUTO_INCREMENT;

# 語法2 CHANGE子句
ALTER TABLE 表名 CHANGE 字段名 字段名 數(shù)據(jù)類型 AUTO_INCREMENT;

3.使用AUTO_INCREMENT時注意事項如下：

一個數(shù)據(jù)表中只能有一個字段設(shè)置AUTO_INCREMENT，設(shè)置AUTO_INCREMENT字段的數(shù)據(jù)類型應該是整數(shù)類型，并且該字段必須設(shè)置了唯一約束或主鍵約束
如果為自動增長字段插入NULL、0、DEFAULT，或在插入數(shù)據(jù)時省略了自動增長字段，則該字段會使用自動增長值；如果插入的是一個具體的值，則不會使用自動增長值。
默認情況下，設(shè)置AUTO_INCREMENT的字段的值會從1開始自增。如果插入了一個大于自動增長值得具體值，則下次插入的自動增長的值會自動使用最大值加1；如果插入的值小于自動增長值，則不會對自動增長值產(chǎn)生影響
使用DELETE語句刪除數(shù)據(jù)時，自動增長值不會減少或填補空缺
在為字段刪除自動增長并重新添加自動增長后，自動增長的初始值會自動設(shè)置為該列現(xiàn)有的最大值加1
在修改自動增長值時，修改的值若小于該列現(xiàn)有的最大值，則修改不會生效

4.示例

1.創(chuàng)建數(shù)據(jù)表，設(shè)置id字段自動增長

mysql> create table my_auto (
    -> id int primary key auto_increment,
    -> username varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

2.使用DESC語句查看表結(jié)構(gòu)，驗證id字段是否成功設(shè)置主鍵和自動增長

mysql> desc my_auto;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

3.添加數(shù)據(jù)進行測試，這里省略id字段，并查詢結(jié)果，從結(jié)果可知省略id字段后id字段會使用自動增長值。從1開始

mysql> select * from my_auto;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
+----+----------+
1 row in set (0.00 sec)

4.添加數(shù)據(jù)進行測試，這里在id字段插入NULL值，從結(jié)果得知id字段使用自動增長值

mysql> insert into my_auto values (null,'b');
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_auto;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
+----+----------+
2 rows in set (0.00 sec)

5.添加數(shù)據(jù)時，在id字段插入具體值5，從結(jié)果可知id字段的值從5開始自增

mysql> insert into my_auto values (5,'c');
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_auto;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  5 | c        |
+----+----------+
3 rows in set (0.00 sec)

6.添加數(shù)據(jù)時，在id字段添加0，使用自動增長，從5開始自增，加一變成6

mysql> insert into my_auto values (0,'d');
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_auto;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  5 | c        |
|  6 | d        |
+----+----------+
4 rows in set (0.00 sec)

7.添加數(shù)據(jù)時，在id字段添加DEFAULT值，使用自動增長

mysql> insert into my_auto values (default,'e');
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_auto;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  5 | c        |
|  6 | d        |
|  7 | e        |
+----+----------+
5 rows in set (0.00 sec)

8.使用SHOW CREATE TABLE語句查看自動增長值，從查詢結(jié)果可知AUTO_INCREMENT=8表明下次插入的自動增長值為8，若下次插入的指定了大于8的值，此處的8會自動更新為下次插入值加1

mysql> show create table my_auto\G
*************************** 1. row ***************************
       Table: my_auto
Create Table: CREATE TABLE `my_auto` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

9.先修改id的自動增長值為10，然后在刪除id字段的自動增長，最后再重新為id字段設(shè)置自動增長

mysql> # 刪除自動增長
mysql> alter table my_auto modify id int;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> desc my_auto;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table my_auto\G
*************************** 1. row ***************************
       Table: my_auto
Create Table: CREATE TABLE `my_auto` (
  `id` int NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> # 重新為id字段添加自動增長
mysql> alter table my_auto modify id int auto_increment;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> desc my_auto;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table my_auto\G
*************************** 1. row ***************************
       Table: my_auto
Create Table: CREATE TABLE `my_auto` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

5.擴展

MySQL中提供了兩個用于維護自動增長的系統(tǒng)變量，分別是AUTO_INCREMENT_MENT和AUTO_INCREMENT_OFFEST，前者表示自增長自動從哪個數(shù)開始，它的取值范圍是1~65535；后者表示自增長字段每次遞增的量。默認為1，取值范圍是1~65535

若要查看這兩個變量的值，可以使用SHOW VARIABLES語句

如：

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

若要改變自動增長的計算方式，可以通過改變這兩個變量的值來實現(xiàn)。例如，可以使用