mysql主键id重置,从1开始计数,初始值

问题

删除数据库记录后，再次新增记录，id继续增加。希望id能够从1开始重新计数。

解决

该表没有外键

truncate table_name;

该表有外键

报错

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID))

解决报错

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table1; 
SET FOREIGN_KEY_CHECKS = 1;

参考文档

http://stackoverflow.com/questions/5452760/truncate-foreign-key-constrained-table