How to achieve automatic increase in mysql

In mysql, you can use the "AUTO INCREMENT" field to achieve automatic increment. This field will generate a unique number when a new record is inserted into the table. The default starting value is 1, and each new record is incremented by 1. The syntax is "field name data type AUTO_INCREMENT".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How does mysql realize automatic increase?

The role of automatic increase:

Problem: After setting the primary key constraint for the data table, each time a record is inserted, if the inserted value already exists , the insertion will fail.

How to solve: Generate an automatically growing value for the primary key.

Auto-grow syntax:

字段名 数据类型 AUTO_INCREMENT；

Usage instructions:

1. There can only be one auto-grow field in a table;

2. This field The data type is an integer type;

3. It must be defined as a key, such as UNIQUE KEY, PRIMARY KEY;

4. If NULL, 0, DEFAULT is inserted for an automatic growth field, or when inserting If this field is omitted, the field will use the automatic growth value;

5. If a specific value is inserted, the automatic growth value will not be used;

6. The automatic growth value starts from 1 Start to increase automatically, adding 1 each time

7. If the inserted value is greater than the automatic growth value, the automatic growth value inserted next time will automatically use the maximum value plus 1;

8. If the inserted value is less than the automatic growth value, it will not have an impact on the automatic growth value;

9. When using DELETE to delete records, the automatic growth value will not reduce or fill the gap.

Automatic growth usage example:

--自动增长使用演示
Create Table my_auto(
id Int Unsigned Primary Key Auto_Increment,
username Varchar(20)
);
#查看
DESC my_auto;

Automatic growth usage demonstration:

#插入时省略id字段，将会使用自动增长值
Insert Into my_auto(username) Values('a');
#为id字段插入null，将会使用自动增长值
Insert Into my_auto Values(Null,'b');
#为id字段插入具体值6
Insert Into my_auto Values(6,'c');
#为id字段插入0，使用自动增长值
Insert Into my_auto Values(0,'d');
#查看
Select * From my_auto;

View automatic growth value:

#查看自动增长值
Show Create Table my_auto;

Modify or delete automatic growth for an existing table:

#修改自动增长值
Alter Table my_auto Auto_Increment=10;
#删除自动增长值
Alter Table my_auto Modify id Int Unsigned;
#重新为id添加自动增长值
Alter Table my_auto Modify id Int Unsigned Auto_Increment;

Note:

1. After automatic growth is deleted and re-added, the initial value of automatic growth will be automatically set to Add 1 to the existing maximum value of the column;

2. 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.

Recommended learning: mysql video tutorial

The above is the detailed content of How to achieve automatic increase in mysql. For more information, please follow other related articles on the PHP Chinese website!