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