Considerations for using the AUTO_INCREMENT column in MySQL: delete data

Source: Internet
Author: User

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!

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.