网站地图    收藏   

主页 > 后端 > mysql数据库 >

MySQL使用AUTO_INCREMENT列的表注意事项之delete数据篇

来源:自学PHP网    时间:2015-04-16 10:50 作者: 阅读:

[导读] 1 说明在对带有AUTO_INCREMENT列的表delete掉所有数据时:(1)对于MyISAM表,在delete表中所有数据时没有任何风险,随意折腾;(2)对于InnoDB表,在delete表中所有数据时,是可能有风险的,...

1. 说明

在对带有AUTO_INCREMENT列的表delete掉所有数据时:

(1)对于MyISAM表,在delete表中所有数据时没有任何风险,随意折腾;

(2)对于InnoDB表,在delete表中所有数据时,是可能有风险的,可能会引入一个大坑,具体看后面的实验。

环境描述:RHEL 6.4 x86_64 + MySQL 5.6.19

blog地址:http://blog.csdn.net/hw_libo/article/details/40149173

在维护有AUTO_INCREMENT列的表时,另外一个注意点,参考:

MySQL使用AUTO_INCREMENT列的表注意事项之delete数据篇

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

2. MyISAM表

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: t7
Create 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=utf8
1 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)


如果在delete from t2后,重启了mysqld,AUTO_INCREMENT并不会被重置:
MySQL [bosco]> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create 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=utf8
1 row in set (0.00 sec)

3. InnoDB表

MySQL [bosco]> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create 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=utf8
1 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;    ## 直接将t2表中的数据全部删除,使用的是delete而非truncate
Query OK, 5 rows affected (0.04 sec)


MySQL [bosco]> insert into t2(name) values('NJ');   ## 再次插入数据时,自增id会自动为下一编号
Query OK, 1 row affected (0.02 sec)


MySQL [bosco]> select * from t2;
+----+------+
| id | name |
+----+------+
|  6 | NJ   |
+----+------+
1 row in set (0.00 sec)


但是,如果在delete from t2后,重启了mysqld,那么情况完全不一样了,这个重启会重置id。
MySQL [bosco]> insert into t2(name) values('FJ');   ## 再次插入数据时,自增id会被重置编号
Query OK, 1 row affected (0.02 sec)


MySQL [bosco]> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | FJ   |  ## 再次插入新值时,id编号会重新从头开始
+----+------+
1 row in set (0.00 sec)

blog地址:http://blog.csdn.net/hw_libo/article/details/40149173

-- Bosco QQ:375612082

---- END ----
-------------------------------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!

自学PHP网专注网站建设学习,PHP程序学习,平面设计学习,以及操作系统学习

京ICP备14009008号-1@版权所有www.zixuephp.com

网站声明:本站所有视频,教程都由网友上传,站长收集和分享给大家学习使用,如由牵扯版权问题请联系站长邮箱904561283@qq.com

添加评论