The related setting and problem _mysql of self-adding auto_increment function in Mysql

The self-auto_increment function in MySQL is believed to be used by every phper, and knows how to set the field, but not all phper know how the auto_increment start value and increment are set! This article for you to share the MySQL field functions of the specific view and set the method.

introduction of the knowledge involved :
The MySQL server maintains 2 MySQL system parameters (System variables): Global variables and Session variables, session variables. Their meanings and differences are as shown in their respective names, the session variables is at the class level, and changes to it will only affect the Session;global variables is system-level, Changes to it affect all new sessions (the session already exists when the change is not affected) until the next MySQL server reboot. Note that its change impact does not span the reboot, and to use the new value when MySQL server restarts, it is only possible to specify by specifying the variable option or a more options file on the command line, and the set change does not reach a span reboot.
Each system variable has a default value that is determined when compiling the MySQL system. The designation of system variables can generally be specified at the command line at server startup or through the option file, and of course, most system variables can be specified by the SET command when the system is running.

first you need to see how to view global variables in MySQL :

Copy Code code as follows:

Show variables like '%xxx% '; <==> Show sessions variables like '%xxx% '; Session Sessions Variable
Show global variables like '%xxx% '; Global variables

For example, to see the variables associated with auto_increment are as follows:

Copy Code code as follows:

Show variables like '%auto_increment% ';

The general result is：

Variables_name	Value
Auto_increment_increment	1
Auto_increment_offset	1

The first variable name auto_increment_increment refers to how much the field increments at one time;
The second variable name Auto_increment_offset refers to the starting value of the self-added field.
For example, there is a table test, field ID as the primary key, self-increase;
If auto_increment_offset=1, and auto_increment_increment=1, then insert the first piece of data into the table, then the id=1 of the data, the second id=2, the third id=3 and so on ...
If auto_increment_offset=2, and auto_increment_increment=10, then insert the first piece of data into the table, then the id=2 of the data, the second id=12, the third id=22 and so on ...
Note: If the value of the Auto_increment_offset is greater than the Auto_increment_increment value, the Auto_increment_offset value is ignored.

Knowing how to view and what it means, the rest is how to modify the values of these variables, and the modification is very simple, the statement format is as follows:
Set auto_increment_increment=10
It's so simple to set the value of the variable auto_increment_increment to 10.
—————————————————————————————————————
to add a seemingly simple but not very simple question：
Q: If there is a table with a field ID's self added primary key, when 10 data has been inserted into the table, delete the data with ID 8,9,10, then restart the MySQL, then insert a data, then the ID value of this data should be how much, is 8, or 11?
A: If the table is of type MyISAM, then it is 11. If the table is of type InnoDB, the ID is 8.
This is because the maximum ID records stored by the two types of storage engines are different in the way that the MyISAM table records the largest IDs in the data file, and the maximum ID value for restarting the MySQL self-added primary key is not lost;
and InnoDB the largest ID value in memory, so restart MySQL or the table after the optimize operation, the maximum ID value will be lost.