Settings and problems related to the auto-increment auto_increment function in mysql

The auto-increment auto_increment function in mysql is believed to have been used by every phper and knows how to set the field to an auto-increment field. However, not all phper know how to set the starting value and increment value of auto_increment! This article will share with you how to view and set the mysql Field Auto-increment function.

Introduction to related knowledge:
The mysql Server maintains two mysql system parameters (system variables): global variables and session variables ). Their meanings and differences are shown in their respective names. session variables are at the session level, and changes to them only affect the current session; global variables are system-level, the change affects all new sessions (the existing session is not affected when the change is made) until the next mysql server restarts. Note that the change impact cannot be different from the restart. To use the new value when mysql server is restarted, you must specify the variable option or change the option file on the command line, however, the cross-Restart mode cannot be implemented through the SET change.
Each system variable has a default value, which is determined when the mysql system is compiled. System variables can be specified in the command line or in the option file when the server is started. Of course, most system variables can be specified during system running, use the set command to specify its value.

First, we need to introduce how to view global variables in mysql.:
Copy codeThe Code is as follows:
Show variables like '% xxx %'; <=> show session variables like '% xxx %'; // session variable
Show global variables like '% xxx %'; // global variable

For example, you can view the variables related to auto_increment as follows:
Copy codeThe Code is as follows:
Show variables like '% auto_increment % ';


Generally, the result is:

Variables_name	Value
Auto_increment_increment	1
Auto_increment_offset	1

The first variable name, auto_increment_increment, indicates the increment of a field;
The second variable name auto_increment_offset indicates the starting value of the auto-increment field.
For example, there is a table test with the field id as the primary key and auto-increment;
If auto_increment_offset = 1 and auto_increment_increment = 1, when the first data entry is inserted into the table, the id of the data entry is 1 and the second id is 2, article 3 id = 3 and so on ......
If auto_increment_offset = 2 and auto_increment_increment = 10, the id of the first data entry is 2 and the id of the second data entry is 12, article 3 id = 22 and so on ......
Note: If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.

After learning about how to view and its meaning, the rest is how to modify the values of these variables. The modification is also very simple. The statement format is as follows:
Set auto_increment_increment = 10
In this simple way, set the value of the variable auto_increment_increment to 10.
-------------------------------------
Adding a seemingly simple but not very simple question:
Q: if there is a table with an auto-increment primary key whose field is id, after 10 data entries have been inserted into the table, the data with id and 10 has been deleted, restart mysql and insert a data record. What is the id of the data record, which is 8 or 11?
A: If the table type is MyISAM, it is 11. If the table type is InnoDB, the id is 8.
This is because the two types of storage engines store the maximum ID records in different ways, MyISAM table records the maximum ID to the data file, the maximum ID value of the auto-incrementing primary key of mysql is not lost;
While InnoDB records the maximum ID value to the memory. Therefore, after you restart mysql or OPTIMIZE the table, the maximum ID value will be lost.