如何正确截断表? [英] How do I truncate tables properly?

问题描述

我正在使用带有ruby的datamapper将数据存储到某些表中.

I'm using datamapper with ruby to store data to certain tables.

几个表具有大量信息，我想在用户重建数据库"时清除它们(它基本上删除了所有内容并重新计算了数据).

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).

我最初尝试使用Forum.all.destroy并针对所有不同的表进行了此操作，但是我注意到其中一些只是未从phpmyadmin中删除.我只能想象它是由于外键.虽然我真的不知道，因为我的其他表中哪个前键已被成功删除.更不用说，id还是将它零"化，这样密钥就不会变成非常大的数字(例如密钥#500,000).

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).

然后我尝试使用下面的代码运行它，但是由于外键约束"，它没有清除表.我想强迫它工作，因为我知道事实上我正在清除所有相互依赖的表(我只是不清除2个表，一个设置表和一个随机存储表，而这两个表都不使用外键).

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).

到目前为止，我有...

So far I have...

adapter = DataMapper.repository(:default).adapter
adapter.execute('TRUNCATE TABLE `forums`, `dates`, `remarks`');

那很好，除非mysql语法显然是错误的.所以这就是第一件事

That would be fine except the mysql syntax is wrong apparently. so thats the first thing

我在phpmyadmin中按1的比例完成了操作，当我以这种方式进行操作时，它说了

I did it 1 by 1 in phpmyadmin and when i did it that way it says

Cannot truncate a table referenced in a foreign key constraint

推荐答案

计划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.

计划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.

这篇关于如何正确截断表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！