MySQL 删除表中的所有行并将 ID 重置为零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12651867/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Delete all rows from table and reset ID to zero
提问by marek_lani
I need to delete all rows from a table but when I add a new row, I want the primary key ID, which has an auto increment, to start again from 0 respectively from 1.
我需要从表中删除所有行，但是当我添加新行时，我希望主键 ID 具有自动增量，分别从 0 开始，分别从 1 开始。
回答by Francois
Do not delete, use truncate:
不要删除，使用truncate：
Truncate table XXX
The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.
表处理程序不记得上次使用的 AUTO_INCREMENT 值，而是从头开始计数。即使对于通常不重用序列值的 MyISAM 和 InnoDB 也是如此。
来源。
回答by a_horse_with_no_name
If you cannot use TRUNCATE(e.g. because of foreign key constraints) you can use an alter table after deleting all rows to restart the auto_increment:
如果您不能使用TRUNCATE（例如由于外键约束），您可以在删除所有行后使用更改表来重新启动 auto_increment：
ALTER TABLE mytable AUTO_INCREMENT = 1
回答by blacky
If table has foreign keys then I always use following code:
如果表有外键，那么我总是使用以下代码：
SET FOREIGN_KEY_CHECKS = 0; -- disable a foreign keys check
SET AUTOCOMMIT = 0; -- disable autocommit
START TRANSACTION; -- begin transaction
/*
DELETE FROM table_name;
ALTER TABLE table_name AUTO_INCREMENT = 1;
-- or
TRUNCATE table_name;
-- or
DROP TABLE table_name;
CREATE TABLE table_name ( ... );
*/
SET FOREIGN_KEY_CHECKS = 1; -- enable a foreign keys check
COMMIT; -- make a commit
SET AUTOCOMMIT = 1 ;
But difference will be in execution time. Look at above Sorin's answer.
但不同之处在于执行时间。看看上面索林的回答。
回答by Sorin
An interesting fact.
一个有趣的事实。
I was sure TRUNCATEwill always perform better, but in my case, for a database with approximately 30 tables with foreign keys, populated with only a few rows, it took about 12 seconds to TRUNCATEall tables, as opposed to only a few hundred milliseconds to DELETEthe rows.
Setting the auto increment adds about a second in total, but it's still a lot better.
我相信TRUNCATE总会有更好的表现，但在我的情况下，大约有30桌外键，人口只有几行的数据库，它花了大约12秒，TRUNCATE所有的表，而不是只有几百毫秒DELETE的行。设置自动增量总共增加了大约一秒钟，但它仍然好得多。
So I would suggest try both, see which works faster for your case.
所以我建议两者都尝试，看看哪个更适合你的情况。
回答by david2020
if you want to use truncateuse this:
如果你想truncate使用这个：
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table $table_name;
SET FOREIGN_KEY_CHECKS = 1;
