MySQL 如何正确截断表格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8641703/
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
How do I truncate tables properly?
提问by Tallboy
I'm using datamapper with ruby to store data to certain tables.
我正在使用带有 ruby 的 datamapper 将数据存储到某些表。
Several of the tables have very large amounts of information and I want to clear them out when the user 'rebuilds database' (it basically deletes everything and re-calculates data).
几个表有非常大量的信息，我想在用户“重建数据库”时清除它们（它基本上删除所有内容并重新计算数据）。
I originally tried Forum.all.destroy and did it for all the different tables, but I noticed some of them just werent deleted from within phpmyadmin. i can only imagine its because of foreign keys. Although I really dont know because my other table which foreing keys was successfully deleted. Not to mention, id rather just 'zero' it out anyway so the keys dont get to extraordinarly large numbers (like key #500,000).
我最初尝试了 Forum.all.destroy 并为所有不同的表做了它，但我注意到其中一些只是没有从 phpmyadmin 中删除。我只能想象它是因为外键。虽然我真的不知道，因为我的另一个表成功删除了哪些前键。更不用说，无论如何，id 而只是将其“归零”，这样键就不会变成非常大的数字（例如键 #500,000）。
I then tried running it with the code below, but it doesnt clear the tables out because of 'foreign key constraints'. I want to force it to work because I know for a fact I'm clearing out all the tables that rely on each other (i'm only not clearing out 2 tables, a settings table and a random storage table, neither of which use foreign keys).
然后我尝试使用下面的代码运行它，但由于“外键约束”，它没有清除表。我想强制它工作，因为我知道我正在清除所有相互依赖的表（我只是没有清除 2 个表，一个设置表和一个随机存储表，这两个表都不使用外键）。
So far I have...
到目前为止我有...
adapter = DataMapper.repository(:default).adapter
adapter.execute('TRUNCATE TABLE `forums`, `dates`, `remarks`');
That would be fine except the mysql syntax is wrong apparently. so thats the first thing
除了 mysql 语法显然是错误的之外，这会很好。所以这是第一件事
I did it 1 by 1 in phpmyadmin and when i did it that way it says
我在 phpmyadmin 中 1 比 1 这样做，当我这样做时，它说
Cannot truncate a table referenced in a foreign key constraint
回答by Devart
Plan A:
方案一：
SET FOREIGN_KEY_CHECKS = 0; -- Disable foreign key checking.
TRUNCATE TABLE forums;
TRUNCATE TABLE dates;
TRUNCATE TABLE remarks;
SET FOREIGN_KEY_CHECKS = 1; -- Enable foreign key checking.
Plan B:
方案B：
You should truncate child tables firstly, then parent tables.
您应该首先截断子表，然后是父表。
Disabling foreign key checks risks entering rows into your tables that don't adhere to the constraints which can cause undefined behavior.
禁用外键检查有可能将不遵守约束的行输入到表中，这可能会导致未定义的行为。
回答by Phoenix
Instead of using Disable foreign key checking.
而不是使用禁用外键检查。
You can use the below code.
您可以使用以下代码。
DELETE FROM forums;
ALTER TABLE forums AUTO_INCREMENT = 1;
DELETE FROM dates;
ALTER TABLE dates AUTO_INCREMENT = 1;
DELETE FROM remarks;
ALTER TABLE remarks AUTO_INCREMENT = 1;
It will just delete all the rows and make id increment from 1 onwards.
它只会删除所有行并使 id 从 1 开始递增。
