526互联

mysql中truncate表对auto_increment的影响

发布时间 2023-08-26 18:55:27作者: abce


在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>