SoFunction
Updated on 2025-04-12

The realization of automatic growth of MySQL data table design

In actual development, it is sometimes necessary to automatically generate primary key values ​​for new records added in the data table. For example, when adding employee information in the employee data table, if you manually fill in the employee work number, you need to check whether the work number is occupied by other employees before adding the employee. Since it takes a while to query first and then add, there may be a problem that the work number is seized by others during concurrent operations. At this time, you can set automatic growth for the employee work number field. After setting automatic growth, if you insert a value into this field, MySQL will automatically generate a unique automatic growth value.

Automatic growth can be achieved by setting AUTO_INCREMENT for the field.

There are two ways to set up automatic growth, namely, set automatic growth when creating data tables and add automatic growth when modifying data tables.

1. Set automatic growth when creating data tables

CREATE TABLE Table name (
Field name Data Type constraint AUTO_INCREMENT,
...
);

2. Add automatic growth when modifying data tables

# Syntax 1 MODIFY clauseALTER TABLE Table name MODIFY Field name Data Type AUTO_INCREMENT;

# Syntax 2 CHANGE clauseALTER TABLE Table name CHANGE Field name Field name Data Type AUTO_INCREMENT;

3. The precautions when using AUTO_INCREMENT are as follows:

  • There can only be one field in a data table to set AUTO_INCREMENT. The data type of the AUTO_INCREMENT field should be an integer type, and the field must have a unique constraint or a primary key constraint.
  • If NULL, 0, DEFAULT is inserted for the automatic growth field, or if the automatic growth field is omitted when inserting data, the field uses the automatic growth value; if a specific value is inserted, the automatic growth value will not be used.
  • By default, the value of the field that sets AUTO_INCREMENT will increase from 1. If a specific value greater than the automatic growth value is inserted, the value of the automatic growth value inserted next time will automatically add 1; if the value inserted is smaller than the automatic growth value, it will not affect the automatic growth value.
  • Automatically increasing values ​​do not decrease or fill gaps when deleting data using DELETE statement
  • After deleting the automatic growth for the field and re-adding the automatic growth, the initial value of the automatic growth is automatically set to the existing maximum value of the column plus 1
  • When modifying the automatic growth value, if the modified value is less than the existing maximum value of the column, the modification will not take effect

4. Example

1. Create a data table and set the id field to automatically grow

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

2. Use DESC statement to view the table structure and verify whether the id field is successfully set to the primary key and automatically grow

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. Add data for testing, omit the id field here and query the results. From the results, we can see that the id field will use the automatic value growth after omitting the id field. Starting from 1

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

4. Add data for testing, insert NULL value in the id field, and learn from the result that the id field uses automatic growth value.

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. When adding data, insert the specific value 5 in the id field. From the result, we can see that the value of the id field starts to increase from 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. When adding data, add 0 in the id field, use automatic growth, start from 5, and increase one to 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. When adding data, add DEFAULT value in the id field and use automatic growth

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. Use the SHOW CREATE TABLE statement to view the automatic growth value. From the query results, we can see that AUTO_INCREMENT=8 indicates that the automatic growth value of the next insert is 8. If the value greater than 8 is specified for the next insert, the 8 here will be automatically updated to the next insert value plus 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. First modify the automatic growth value of id to 10, then delete the automatic growth of the id field, and finally set the automatic growth for the id field again.

mysql> # Delete automatic growthmysql> 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> # Add automatic growth to the id field againmysql> 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. Extended

MySQL provides two system variables for maintaining automatic growth, namely AUTO_INCREMENT_MENT and AUTO_INCREMENT_OFFEST. The former indicates which number the self-growth automatically starts, and its value range is 1~65535; the latter indicates the amount of each increment of the self-growth field. The default is 1, and the value range is 1~65535

To view the values ​​of these two variables, you can use the SHOW VARIABLES statement

like:

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)

To change the calculation method of automatic growth, it can be achieved by changing the values ​​of these two variables. For example, you can use

SET @@auto_increment_increment = 10;

Change the value of auto_increment_increment to 10. Here we only introduce how to view the value of automatic growth system variables. The relevant content about variables will be learned in the function module in the database programming chapter.

This is the end of this article about the implementation of automatic growth of MySQL data table design. For more related content on MySQL automatic growth, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!