TRUNCATE后索引被禁用 [英] Index disabled after TRUNCATE
问题描述
我有一系列表格可以从文件中刷新数据。
在刷新数据之前，使用TRUNCATE或DELETE清除某些表。 br />
如果使用TRUNCATE，任何非PRIMARY或UNIQUE的索引似乎都被禁用，并且在将新数据插入表格时不会更新。
执行OPTIMIZE重新激活索引。
如果使用DELETE而不是TRUNCATE，则不会出现此问题。
是否有人知道这是一个错误，一个功能，一个可以改变的选项吗？
MySQL版本4.1.20
I have a series of tables in which the data can be refreshed from a file.
Before refreshing the data, some of the tables are cleared using either TRUNCATE or DELETE.
If TRUNCATE is used, any index which is not PRIMARY or UNIQUE appears to be disabled and is not updated when new data is inserted into the table.
Performing an OPTIMIZE reactivates the index.
If DELETE is used instead of TRUNCATE, this problem does not occur.
Does anyone know if this a fault, a feature, an option which can be changed?
MySQL version 4.1.20
推荐答案
这可能与TRUNCATE实际丢弃并重新创建表的事实有关。但是，它看起来并不应该这样做，而且我无法对谷歌做太多关于它的事情。试着看看你是否可以在一个单独的数据库中可靠地重现这种行为。
您还应该能够通过以下方式重新启用索引：
This probably has something to with the fact that TRUNCATE actually drops and recreates the table. It does not seem like it is supposed to do that, though, and I wasn''t able to Google much about it. Try to see if you can reliably reproduce this behavior in a separate database.
You should also be able to re-enable the indexes by doing:
MySQL 4.1里面有很多古怪的特征。我建议升级到5.
MySQL 4.1 has a lot of quirky ''features'' in it. I would suggest upgrading to 5.
MySQL 4.1中有很多古怪的功能。我建议升级到5.
MySQL 4.1 has a lot of quirky ''features'' in it. I would suggest upgrading to 5.
感谢您的建议，但目前不是一个选项。
我是在共享主机系统上运行，因为我丢失了几个索引，我的查询开始表现不佳（MySQL服务器超载），我的MySQL数据库被关闭 - 两次！
它只是在第二次出现之后，我设法获得足够的信息以找出造成问题的原因。
哦，我刚刚刚刚注册了这个主机 - 前一个被暂停我的帐户完全（电子邮件，网络，FTP，我的SQL），再次为MySQL服务器过载，大概是出于同样的原因。
我现在有一个workround，但你可以看到为什么我很想了解原因。
Thanks for the advice, but not currently an option.
I''m running on a shared hosting system and because I was losing several indexes, my queries started performing badly (MySQL server overload), and my MySQL database was turned off - twice!
It was only after the second occurrence that I managed to get enough information to find out what was causing the problem.
Oh, and I have only just recently signed up with this host - the previous one suspended my account completely (e-mail, web, FTP, My SQL), again for MySQL server overload, presumably for the same reason.
I have a workround now, but you can see why I''m keen to understand the cause.
这篇关于TRUNCATE后索引被禁用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
