Reset Auto_increment Self-increment method in MySQL

In general, self-appreciation is mainly a data table primary key or a unique field, in MySQL can be automatically generated by the Auto_increment property of the data column.

You can use "auto_increment=n" to specify an incremental initial value when you are building a table, such as:

CREATE TABLE Test
(
ID INT UNSIGNED not NULL PRIMARY KEY auto_increment,
Username VARCHAR () not NULL
) auto_increment = 100;

This example creates a test table with the primary key ID set to the self increase, with an initial value of 100.

The ALTER TABLE table_name AUTO_INCREMENT=N command can be reset when we need to modify the self add starting value.
For example, table test has a maximum ID of 150, but for some reason the value added has been 250, so it needs to be reset to 151 to increase the next ID starting at 151 and the syntax is as follows:

ALTER TABLE Test auto_increment=151

Description

(1) If you insert a null into a auto_increment data column, MySQL automatically generates the next sequence number. The numbering starts at 1 and 1 is the base increment.

(2) The effect of inserting 0 into a auto_increment data column is the same as inserting a null value. This is not recommended, however, or it is better to insert a null value.

(3) The equivalent of inserting a null value when the record is inserted without explicitly specifying a value for auto_increment.

(4) When inserting a record, if you explicitly specify a numeric value for the Auto_increment data column, two situations occur, in which case, if the inserted value repeats with the existing number, an error message occurs because the value of the Auto_increment data column must be unique; condition two, If the inserted value is greater than the numbered value, the value is inserted into the data column, and the next number is incremented from the new value. In other words, you can skip some numbers.

(5) If you update your own column with the update command, an error occurs if the column value repeats with the existing value. If it is greater than an existing value, the next number is incremented from that value.

How to modify MySQL's auto_increment_increment global variable, self increment step

To understand the command of global variables, the following commands are logged in MySQL after operation:

#查看auto_inc开头的全局变量, the 2 variables of auto_increment_increment and Auto_increment_offset.
Mysql> show VARIABLES like ' auto_inc% ';

Setting Global variables
Method One:

#设置auto_increment_increment自增步长为n, that is, each insert a piece of data, add N, this n must be a number. The default is 1
mysql> SET @ @auto_increment_increment =n;

#设置auto_increment_offset自增开始数字为m, when you create a new table that is familiar, and represents an empty. Self-increasing number defaults starting from M
mysql> SET @ @auto_increment_offset =m;

If the above method does not work, or restart MySQL, and then changed back. That must have been a global variable set in the MY.CNF. This must be modified in the configuration file; and cannot be modified again through the above operation.

Method Two:

Open configuration file with VI editor, default location
#vi/etc/my.cnf

Find the place where the auto_increment_increment variable is set; VI inside can be found with "/auto_increment_increment".

After finding settings
Auto_increment_increment=1;

Other global variables can also be permanently set in this configuration file.
After the configuration file has been modified, the MySQL service will not take effect until it is restarted.
#/etc/init.d/mysql restart