MySQL table considerations for using the Auto_increment column Delete data sheet

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.