mysql AUTO_INCREMENT delete/truncate table 操作影响的一点理解

最新推荐文章于 2022-04-15 22:54:43 发布

lixora

最新推荐文章于 2022-04-15 22:54:43 发布

阅读量647

点赞数

分类专栏： MySql

本文链接：https://blog.csdn.net/lixora/article/details/97112659

版权

MySql 专栏收录该内容

83 篇文章 2 订阅

订阅专栏

delete table操作不会重置AUTO_INCREMENT 值；

truncate table 操作会重置AUTO_INCREMENT 值；

mysql> delete from t;
Query OK, 5 rows affected (0.00 sec)

mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
| t | CREATE TABLE `t` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> truncate table t;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
| t | CREATE TABLE `t` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql>