MySQL table considerations for using the Auto_increment column Delete data sheet

1. Description

When you delete all data for a table with auto_increment columns:

(1) For the MyISAM table, the delete table in all data without any risk, random toss;

(2) for the InnoDB table, in the Delete table all the data, it is possible to risk, may introduce a big pit, concrete look at the following experiment.

Environment Description: RHEL 6.4 x86_64 + MySQL 5.6.19

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

When maintaining a table with auto_increment columns, another point of attention, refer to:

MySQL table considerations for using the Auto_increment column Delete data sheet

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

2. MyISAM table

MySQL [bosco]> CREATE TABLE ' T7 ' (-id ' int ') unsigned not NULL auto_increment, ' name ' varchar ( ) 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:0mysql [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 (ten) unsigned not NULL auto_incr Ement, ' name ' varchar (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) If you restart mysqld,auto_increment after the delete from T2, it will not be reset: MySQL [bosco]> Show Create Table t7\g*************************** 1. Row *************************** table:t7create table:create Table ' T7 ' (' id ' int (ten) unsigned not NULL auto_incr Ement, ' name ' varchar (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 (ten) unsigned not NULL auto_incr Ement, ' name ' varchar (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:0mysql [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; # # directly Delete all data from the T2 table, using delete instead of Truncatequery OK, 5 rows affected (0.04 sec) MySQL [bosco]> insert into T2 (name) VALUES (' N   J '); # # when inserting data again, the self-increment ID will automatically be the next number of 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 the delete from T2, then the situation is completely different, and this reboot resets the ID.   MySQL [bosco]> INSERT into T2 (name) VALUES (' FJ '); # # when inserting data again, the self-increment ID will be reset number query OK, 1 row affected (0.02 sec) MySQL [bosco]> select * FROM t2;+----+------+| ID |  Name |+----+------+| 1 |  FJ | # # When you insert a new value again, the ID number will start again from the beginning +----+------+1 row in Set (0.00 sec)

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

--Bosco qq:375612082

----END----
-------------------------------------------------------------------------------------------------------
Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal responsibility!

MySQL table considerations for using the Auto_increment column Delete data sheet