MySQL modifies the value of auto_increment

You can see the ID field at this time the increment is starting at 1000 and has grown to 1002

select * from K1;

+------+--------+

| ID | name |

+------+--------+

| 1000 | Xiaoke |

| 1001 | Xiaoke |

| 1002 | Xiaoke |

+------+--------+

Take a look at this table statement

Show create table K1;

+-------+------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------+

| Table |                                                                                                                                                                  Create Table |

+-------+------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------+

| K1 | CREATE TABLE ' K1 ' (

' id ' int (one) not NULL auto_increment,

' Name ' varchar (+) DEFAULT NULL,

PRIMARY KEY (' id ')

) engine=innodb auto_increment=1003 DEFAULT Charset=utf8 |

At this point, we want to set the ID to start with 1, using the following statement:

ALTER TABLE K1 auto_increment=1;

Query OK, 0 rows Affected (0.00 sec)

records:0 duplicates:0 warnings:0

Reinsert a statement

Insert into K1 (name) VALUES (' Xiaoke ');

At this point, we re-check the contents of the K1 table, there are 4 items

select * from K1;

+------+--------+

| ID | name |

+------+--------+

| 1000 | Xiaoke |

| 1001 | Xiaoke |

| 1002 | Xiaoke |

| 1003 | Xiaoke |

+------+--------+

As you can see, the ID field of the K1 table does not start with the 1 we set!!

Let's do this one more time, set the table field to start with 10001, using the following statement:

ALTER TABLE K1 auto_increment=;

Let's look at the contents of the K1 table again.

select * from K1;

+-------+--------+

| ID | name |

+-------+--------+

| 1000 | Xiaoke |

| 1001 | Xiaoke |

| 1002 | Xiaoke |

| 1003 | Xiaoke |

| 10001 | Xiaoke |

As you can see, this time our setup has come into effect, then through the verification instructions,

ALTER TABLE table_name AUTO_INCREMENT=N

Set the self-increment of the table's initial value, can only set the auto_increment= than the ID field maximum value is also larger, can not be successful, can not set auto_increment than the current ID value small!

In the case where the ID field already has data, if we want Auto_increment to start from 1, we can delete the ID field and then add the ID field again, using the following statement

ALTER TABLE K1 drop id;alter table k1 add ID int (one) NOT null PRIMARY key auto_increment First;select * from K1;

+----+--------+

| ID | name |

+----+--------+

| 1 | Xiaoke |

| 2 | Xiaoke |

| 3 | Xiaoke |

| 4 | Xiaoke |

| 5 | Xiaoke |

+----+--------+

This article is from the "Xiao Ke" blog, please be sure to keep this source http://5540023.blog.51cto.com/5530023/1891110

MySQL modifies the value of auto_increment