About MySQL self-increment field issues

Recently encountered the MySQL field of the self-enhancement problem, need to temporarily deal with, and then by the way to make up for the missed lesson, so that there is an article.

1. What is self-increment?

He is a field property that is used to create a uniquely identified column.

The Auto_increment attribute can be used to generate a unique identity for new rows:

Shell

CREATE TABLE Animals (ID mediumint not NULL auto_increment, name CHAR (+) NOT NULL, PRIMARY KEY (ID)) Engine=innodb;inser T into Animals (name) VALUES (' Dog '), (' Cat '), (' Penguin '), (' lax '), (' Whale '), (' Ostrich '); SELECT * from animals; which returns:+----+---------+| ID | Name    |+----+---------+|  1 | Dog     | |  2 | Cat     | |  3 | Penguin | |  4 | LAX     | |  5 | Whale   | |  6 | Ostrich |+----+---------+

He has the following characteristics

Shell

1. The only and sequential, insert or delete even update is counted, or I understand the action count instead of the value Count 2. The maximum value of the field above itself cannot be written, and an error will be shown, such as a key repeat duplicate entry

How to view this property (three methods)

• Show CREATE TABLE Wp_options (example)

Shell

CREATE TABLE ' wp_options ' (    ' option_id ' bigint () unsigned not NULL auto_increment,    ' option_name ' varchar (64) Not null default ' ",    ' option_value ' longtext not NULL,    ' autoload ' varchar (a) NOT null default ' Yes ',

• Show Table Status

• SELECT table_name,auto_increment from Information_schema.tables WHERE table_schema= ' WP ';

Btw

Official mention: You can use alter TABLE tbl auto_increment = 100, to restore the initial value, but in fact, there is no clear, this value has an internal count function, so if the situation is encountered this value of this used, but later deleted, will continue to accumulate added up, but there are One case, if now count to 100, and I insert a 1000, then it is possible to restore 1000 to 101 by this statement.

In addition, the official said: Auto_increment will also distinguish between InnoDB and Myisam,myisam can use multiple fields as a auto_increment, and innodb not, whereby I understand that because of multiple fields, So the unique attribute is magnified to the unique value of a combination of 2 fields, so the ID field can be reused, and InnoDB can only be auto_increment by a single field, so if you insert data or import data at this time, the auto_increment in the data The value of the field is often duplicated

Shell

CREATE TABLE Animals (GRP ENUM (' Fish ', ' mammal ', ' bird ') not null,id mediumint not NULL auto_increment,name CHAR (+) not NUL L,primary KEY (grp,id)) Engine=myisam;insert into Animals (grp,name) VALUES (' Mammal ', ' dog '), (' Mammal ', ' cat '), (' Bird ', ' Penguin '), (' Fish ', ' lax '), (' Mammal ', ' whale '), (' Bird ', ' ostrich '); SELECT * from animals ORDER by grp,id; which returns:+--------+----+---------+| GRP    | id | name    |+--------+----+---------+| Fish   |  1 | LAX     | | mammal |  1 | Dog     | | mammal |  2 | Cat     | | mammal |  3 | Whale   | | Bird   |  1 | Penguin | | Bird   |  2 | Ostrich |

2. The situation I encountered is the need to import data, and there is self-added value in the block import, because the direct import will be the key duplication (InnoDB)

If we need to completely reset this auto_increment count, there are 2 ways:

1. TRUNCATE TABLE name (this will not only delete all the data, but also reposition the increment field)

2. Delete the Auto_increment field and then re-build the field and grant the Auto_increment property so that it will be reordered

Shell

ALTER table name DROP ID;    ALTER TABLE name add ID int (one)  null first;    ALTER table name MODIFY COLUMN ID int (one) not NULL Auto_increment,add PRIMARY KEY (ID);

• This article is from: Linux Tutorial Network

About MySQL self-increment field issues