Considerations for using the AUTO_INCREMENT column in MySQL: delete data

Considerations for using the AUTO_INCREMENT column in MySQL: delete data
1. Description

When deleting all data from a table with the AUTO_INCREMENT column:

(1) For the MyISAM table, there is no risk in deleting all the data in the table;

(2) For InnoDB tables, all data in the delete table may be at risk, and a big pitfall may be introduced. For details, refer to the experiment below.

Environment Description: RHEL 6.4 x86_64 + MySQL 5.6.19

Blog: http://blog.csdn.net/hw_libo/article/details/40149173

When maintaining tables with AUTO_INCREMENT columns, for more information, see:

Considerations for using the AUTO_INCREMENT column in MySQL: delete data

Http://blog.csdn.net/hw_libo/article/details/40097125

2. MyISAM table

MySQL [bosco]> create table 't7 '(-> 'id' int (10) unsigned not null AUTO_INCREMENT,-> 'name' varchar (20) not null default '', -> primary key ('id')->) ENGINE = MyISAM; Query OK, 0 rows affected (0.05 sec) MySQL [bosco]> insert into t7 (name) values ('gz '), ('sh'), ('bj'), ('sz '), ('hz'); Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 MySQL [bosco]> select * from t7; + ---- + ------ + | id | name | + ---- + ------ + | 1 | GZ | 2 | SH | 3 | BJ | 4 | SZ | 5 | HZ | + ---- + ------ + 5 rows in set (0.00 sec) mySQL [bosco]> delete from t7; Query OK, 5 rows affected( 0.03 sec) mySQL [bosco]> show create table t7 \ G **************************** 1. row *************************** Table: t7Create Table: create table 't7 '('id' int (10) unsigned not null AUTO_INCREMENT, 'name' varchar (20) not null default '', primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 6 default charset = utf81 row in set (0.00 sec) MySQL [bosco]> insert into t7 (name) values ('nj '); Query OK, 1 row affected (0.07 sec) MySQL [bosco]> select * from t7; + ---- + ------ + | id | name | + ---- + ------ + | 6 | NJ | + ---- + ------ + 1 row in set (0.00 sec) after delete from t2, after mysqld is restarted, AUTO_INCREMENT is not Reset: mySQL [bosco]> show create table t7 \ G **************************** 1. row *************************** Table: t7Create Table: create table 't7 '('id' int (10) unsigned not null AUTO_INCREMENT, 'name' varchar (20) not null default '', primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 6 default charset = utf81 row in set (0.00 sec)

3. InnoDB table

MySQL [bosco]> show create table t2 \ G **************************** 1. row *************************** Table: t2Create Table: create table 't2' ('id' int (10) unsigned not null AUTO_INCREMENT, 'name' varchar (20) not null default '', primary key ('id ')) ENGINE = InnoDB default charset = utf81 row in set (0.00 sec) MySQL [bosco]> insert into t2 (name) values ('gz '), ('sh '), ('bj '), ('sz'), ('hz '); Query OK, 5 rows Affected (0.04 sec) Records: 5 Duplicates: 0 Warnings: 0 MySQL [bosco1]> select * from t2; + ---- + ------ + | id | name | + ---- + ------ + | 1 | GZ | 2 | SH | 3 | BJ | 4 | SZ | 5 | HZ | + ---- + ------ + 5 rows in set (0.00 sec) mySQL [bosco]> delete from t2; # delete all data in table t2 directly, using delete instead of truncateQuery OK, 5 rows affected (0.04 sec) mySQL [bosco]> insert into t2 (name) values ('nj '); # when data is inserted again, the auto-increment id will automatically be the next number Query OK, 1 row affected (0.02 sec) MySQL [bosco]> select * from t2; + ---- + ------ + | id | name | + ---- + ------ + | 6 | NJ | + ---- + ------ + 1 row in set (0.00 sec) However, if mysqld is restarted after delete from t2, the situation is completely different. This restart will reset the id. MySQL [bosco]> insert into t2 (name) values ('fj '); # when data is inserted again, the auto-increment id is reset to Query OK, 1 row affected (0.02 sec) MySQL [bosco]> select * from t2; + ---- + ------ + | id | name | + ---- + ------ + | 1 | FJ | # when a new value is inserted again, the id number starts from scratch again + ---- + ------ + 1 row in set (0.00 sec)

Blog: http://blog.csdn.net/hw_libo/article/details/40149173

-- Bosco QQ: 375612082

---- END ----
Bytes -------------------------------------------------------------------------------------------------------
All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!