The Settings and problems related to the self incrementing auto_increment function in mysql

  • 2020-05-14 05:13:07
  • OfStack

The self-incrementing auto_increment function in mysql is believed to have been used by every phper and knows how to set the field as a self-incrementing field, but not all phper know how to set the starting value and increment of auto_increment! This article is for you to share 1 under the mysql field from the function of the specific view and Settings.

Introduction of knowledge involved:
The mysql server maintains two mysql system parameters (system variables) : the global variable (global variables) and the session variable (session variables). Their meanings and differences are indicated by their respective names. session variables is at session level, and any change to session variables will only affect session. global variables is system level, and changes to it will affect all new session (session existing at the time of the change is not affected) until the next mysql server reboot. Note that its change impact cannot be rebooted across reboots. To use the new value when mysql server is rebooted, you can only do so by specifying variable options on the command line or by changing the options file. Changes via SET cannot be rebooted across reboots.
Each system variable has a default value, which is determined when the mysql system is compiled. System variables can be specified either on the command line or via the options file at startup of server. Of course, most system variables can be specified by the set command at runtime.

First of all, we need to introduce 1 method to view global variables in mysql:

 
show variables like '%xxx%'; <==> show session variables like '%xxx%'; //session Session variable  
show global variables like '%xxx%'; // The global variable  

For example, the method to view the variables associated with auto_increment is as follows:
 
show variables like '%auto_increment%'; 


1. General results are:
variables_name value auto_increment_increment 1 auto_increment_offset 1
The first variable name auto_increment_increment is the number of increments of the field.
The second variable name auto_increment_offset refers to the starting value of the self-incrementing field.
For example, there is a table test, and the field id is the primary key, which is self-incrementing;
If auto_increment_offset=1, and auto_increment_increment=1, then when the first data is inserted into the table, id=1, id=2, id=3, and so on...
If auto_increment_offset=2 and auto_increment_increment=10, then when the first data is inserted into the table, id=2, id=12, id=22 and so on...
Note: if the value of auto_increment_offset is greater than the value of auto_increment_increment, the value of auto_increment_offset is ignored.

Once you know how to view them and what they mean, all that remains is how to modify the values of these variables, which is also very simple. The format of the statement is as follows:
set auto_increment_increment=10
Simply set the value of the variable auto_increment_increment to 10
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
Add a seemingly simple but not so simple question:
Q: if you have a table with a self-increment primary key for id, and you have inserted 10 pieces of data into the table, delete the data with id of 8, 9, 10, restart mysql, and insert another piece of data, what should the id value of this data be, 8 or 11?
Answer: if the table is of type MyISAM, then it is 11. If the table is of type InnoDB, id is 8.
This is because the two types of storage engines store the maximum ID records in different ways. The MyISAM table records the maximum ID in a data file, and the maximum ID value of the self-increment primary key of the restart mysql is not lost.
While InnoDB records the maximum ID value in memory, so the maximum ID value will be lost when mysql is restarted or OPTIMIZE is performed on the table.


Related articles: