mysql中truncate表对auto_increment的影响
2023-08-26 18:47 abce 阅读(216) 评论(0) 编辑 收藏 举报
在mysql中,如果对表执行truncate操作后,会重新设置auto_increment的值,比如:
root@localhost (none)>use abce; Database changed root@localhost abce>create table test(id int not null auto_increment primary key,age int); Query OK, 0 rows affected (0.02 sec) root@localhost abce>insert into test(age) values(3),(4),(5); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 root@localhost abce>select * from test; +----+------+ | id | age | +----+------+ | 1 | 3 | | 2 | 4 | | 3 | 5 | +----+------+ 3 rows in set (0.00 sec) root@localhost abce>
对表执行truncate操作:
root@localhost abce>truncate table test; Query OK, 0 rows affected (0.02 sec) root@localhost abce>insert into test(age) values(6),(7),(8); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 root@localhost abce>select * from test; +----+------+ | id | age | +----+------+ | 1 | 6 | | 2 | 7 | | 3 | 8 | +----+------+ 3 rows in set (0.01 sec) root@localhost abce>
可以看到自增列又从1重新开始了。
但是有时候,因为数据之间的依赖等原因,不希望重复使用相同的自增值作为主键。这个时候可以使用alter table来重新设置auto_increment的值。例如:
root@localhost abce>truncate table test; Query OK, 0 rows affected (0.03 sec) root@localhost abce>alter table test auto_increment=4; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost abce>insert into test(age) values(6),(7),(8); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 root@localhost abce>select * from test; +----+------+ | id | age | +----+------+ | 4 | 6 | | 5 | 7 | | 6 | 8 | +----+------+ 3 rows in set (0.01 sec) root@localhost abce>