MySQL 在 Magento 中安全截断的表列表？

List of tables to safely truncate in Magento?

提问by Jim OHalloran

Is there a list of tables that can be safely truncated in Magento? By safely, I mean to preserve products.

是否有可以在 Magento 中安全截断的表列表？安全，我的意思是保存产品。

I've got a few but want to know if there are more:

我有一些，但想知道是否还有更多：

    core_url_rewrite # Only safe if no custom rewrites are in place
    catalog_product_flat_1
    catalog_product_flat_# (# depends on the multistore)
    log_customer
    log_quote
    log_summary
    log_summary_type
    log_url
    log_url_info
    log_visitor
    log_visitor_info
    log_visitor_online

Please note that this question is almost 8 years old now; do not just start truncating tables randomly; it's probably in your best interest to backup before you do anything.

请注意，这个问题现在已经快 8 年了；不要只是开始随机截断表格；在您做任何事情之前进行备份可能符合您的最大利益。

回答by Steve Robbins

Before you do anything

在你做任何事情之前

• Make sure you test clearing this data in a non-production environment first.
• Always make backups before you lose data forever.
• Make sure you're truncateing, not droping.
• Probably a good idea to reindex everything via shell after mass deleting records

• 确保首先在非生产环境中测试清除此数据。
• 在永远丢失数据之前始终进行备份。
• 确保你在truncateing，而不是droping。
• 在大量删除记录后通过 shell 重新索引所有内容可能是个好主意

---

---

Update:

更新：

You can use this n98-magerunmoduleto clean up your tables.

你可以使用这个n98-magerun模块来清理你的表。

Or do it manually by following instructions below.

或者按照以下说明手动执行此操作。

---

---

To expand on Jim's answer, Magento Support doesn't need the contents of these tables when they ask for a copy of your DB, so you could consider them non-essential.

为了扩展 Jim 的回答，Magento Support 在要求您的数据库副本时不需要这些表的内容，因此您可以认为它们不是必需的。

Cache tables

缓存表

core_cache
core_cache_tag

Cache data is temporary. Clearing these should be safe.

缓存数据是临时的。清除这些应该是安全的。

Session tables

会话表

core_session

No need to keep year old sessions. New sessions will automatically be created (though it will cause people to be logged out/break a current checkout flow).

无需保留一年前的会话。将自动创建新会话（尽管它会导致人们注销/中断当前的结帐流程）。

Dataflow tables

数据流表

dataflow_batch_export
dataflow_batch_import

There are essentially logs of each time a batch is run and not critical.

每次运行批处理时基本上都有日志，并不重要。

Admin logs

管理员日志

enterprise_logging_event
enterprise_logging_event_changes

These are logs of which admins are doing what in the backend. Very nice for tracking down "who broke what" but don't need to be kept forever. You can safely truncate these.

这些是管理员在后端做什么的日志。非常适合追踪“谁破坏了什么”，但不需要永远保留。您可以安全地截断这些。

Pro-tip: Make sure you're cleaning out old records in System > Configuration > Advanced > System > Admin Actions Log Archiving

专业提示：确保您在系统 > 配置 > 高级 > 系统 > 管理操作日志存档中清除旧记录

Support tables

支撑台

enterprise_support_backup
enterprise_support_backup_item

History of support from Magento, may or may not exist for you.

Magento 的支持历史，对您来说可能存在，也可能不存在。

Index tables

索引表

index_event
index_process_event

A back log of index entries that need to be updated. However, they don't delete themselves once they're obsolete.

需要更新的索引条目的积压日志。但是，一旦它们过时，它们就不会删除自己。

Log tables

日志表

log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online

Log data, mostly unused. However, I've seen "Sort by Most Viewed" modules use the log_visitor_infotable so be cautious.

日志数据，大多未使用。但是，我已经看到“按查看次数最多的排序”模块使用该log_visitor_info表，所以要小心。

Pro-tip: Make sure you're cleaning out old records in System > Configuration > Advanced > System > Log Cleaning(this only does visitors, customers, and urls)

专业提示：确保您在“系统”>“配置”>“高级”>“系统”>“日志清理”中清除旧记录（这只对访问者、客户和 URL 有效）

Report tables

报告表

report_event
report_viewed_product_index

These are aggregated tables that can be rebuilt when running reports.

这些是可以在运行报告时重建的聚合表。

---

---

Other tables that can use a pruning once in a while are

其他可以偶尔使用修剪的表是

Quote tables

报价表

sales_flat_quote
sales_flat_quote_address
sales_flat_quote_address_item
sales_flat_quote_item
sales_flat_quote_item_option
sales_flat_quote_payment
sales_flat_quote_shipping_rate

If having 3 year old abandoned cart data isn't important to you, consider truncating these. Keep in mind that current carts are in here, so schedule this during off hours or remove rows with updated_atolder than X days.

如果拥有 3 年的废弃购物车数据对您来说并不重要，请考虑截断这些数据。请记住，当前的购物车在此处，因此请在下班时间安排此操作或删除updated_at超过 X 天的行。

Pro-tip: install Aoe_QuoteCleaner

专业提示：安装Aoe_QuoteCleaner

Staging tables

临时表

If you use Enterprise's staging feature, you might start seeing tables with the s_prefix. There is no clean up for these once the staging site is removed. If your enterprise_stagingtable is empty, you don't need these tables anymore.

如果您使用 Enterprise 的暂存功能，您可能会开始看到带有s_前缀的表。一旦暂存站点被删除，就不会对这些进行清理。如果您的enterprise_staging表是空的，则不再需要这些表。

Changelog tables

变更日志表

catalog_category_flat_cl
catalog_category_product_cat_cl
catalog_category_product_index_cl
catalog_product_flat_cl
catalog_product_index_price_cl
cataloginventory_stock_status_cl
catalogsearch_fulltext_cl
enterprise_url_rewrite_category_cl
enterprise_url_rewrite_product_cl
enterprise_url_rewrite_redirect_cl

Magento introduced MySQL triggers that write to change log tables when certain tables' data is modified. Later the scheduler indexer picks up the change log entries and updates the items. However, it doesn't clean up when it's done. You can clear these out from time to time.

Magento 引入了 MySQL 触发器，当某些表的数据被修改时，它会写入更改日志表。稍后调度程序索引器会选取更改日志条目并更新项目。但是，它在完成后不会清理。您可以不时清除这些。

Category and product flat tables

品类和产品平面表

catalog_category_flat_store_1
catalog_category_flat_store_2
catalog_category_flat_store_3
catalog_category_flat_store_4
catalog_category_flat_store_5
catalog_category_flat_store_6
catalog_category_flat_store_7
catalog_product_flat_1
catalog_product_flat_2
catalog_product_flat_3
catalog_product_flat_4
catalog_product_flat_5
catalog_product_flat_6
catalog_product_flat_7

These tables I tend to drop. After a reindex they will re-create themselves. In some cases store 7might not exist anymore but you still have the dead flat table.

这些表我倾向于drop。重新索引后，他们将重新创建自己。在某些情况下，商店7可能不再存在，但您仍然拥有死板。

URL rewrite tables

URL 重写表

Be careful here, you may not want to truncate all of these.

在这里要小心，您可能不想截断所有这些。

core_url_rewrite
enterprise_url_rewrite

First check for any records that are is_system = 0. If so you won't want to truncate, you'll lose custom redirects. Try DELETE FROM core_url_rewrite WHERE is_system = 1instead. Reindexing rewrites will re-populate this table with the rest.

首先检查任何记录是is_system = 0. 如果是这样，您不想截断，您将丢失自定义重定向。试试吧DELETE FROM core_url_rewrite WHERE is_system = 1。重新索引重写将使用其余部分重新填充此表。

More report tables

更多报告表

report_viewed_product_aggregated_daily
report_viewed_product_aggregated_monthly
report_viewed_product_aggregated_yearly

These are aggregated and can be rebuilt (like indexes).

这些是聚合的并且可以重建（如索引）。

回答by Jim OHalloran

When you log an issue with Magento support and they ask you to provide a database dump, the script they give you dumps the schema only for the following tables:

当您使用 Magento 支持记录问题并且他们要求您提供数据库转储时，他们给您的脚本仅转储以下表的模式：

core_cache
core_cache_option
core_cache_tag
core_session
dataflow_batch_export
dataflow_batch_import
enterprise_logging_event
enterprise_logging_event_changes
enterprise_support_backup
enterprise_support_backup_item
index_event
index_process_event
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online
report_event
report_viewed_product_index

If Magento support doesn't need the contents of these tables to resolve issues, it would be a safe assumption that they can be safely truncated.

如果 Magento 支持不需要这些表的内容来解决问题，那么可以安全地假设它们可以被安全地截断。

The catalog_product_flat_*tables and catalog_category_flat_*tables can also be truncated as a reindex will re-populate them.

该catalog_product_flat_*表和catalog_category_flat_*表也被截断为重建索引将重新填充它们。

A user can add entries to the core_url_rewritetable manually from the back end and I wouldn't like to guarantee that two products pr categories with identical URL keys will always have the same URLs after truncating core_url_rewrite. It's not one I'd rely on being able to truncate safely.

用户可以core_url_rewrite从后端手动向表中添加条目，我不想保证具有相同 URL 键的两个产品 pr 类别在截断后始终具有相同的 URL core_url_rewrite。这不是我能够安全截断的依赖。

回答by augsteyer

I want to add to the list that you can also truncate "catalogrule_product" and "catalogrule_product_price". You can regenerate it by running Apply Rules in Pormos > Catalog Rules. I have truncated this table quite a few times to know that it's safe. NB! All your catalog rule prices will disappear from the frontend until you re-run the rules.

我想添加到列表中，您还可以截断“catalogrule_product”和“catalogrule_product_price”。您可以通过在 Pormos > 目录规则中运行应用规则来重新生成它。我已经多次截断这张表以知道它是安全的。注意！您所有的目录规则价格都将从前端消失，直到您重新运行规则。

I would also love to see if someone can describe what happens with the site if these tables are cleared. E.g. I assume that dropping core_session (if we are using database to store those) will drop all customer current "logged in" sessions, will it also drop carts of guests?

我也很想看看如果这些表格被清除，是否有人可以描述网站会发生什么。例如，我假设删除 core_session（如果我们使用数据库来存储这些会话）将删除所有客户当前“登录”的会话，它还会删除客人的购物车吗？

回答by limex

I doubt that it is useful to truncate the i.e. admin_* tables. Which is done if you follow the above list of the only worthy tables. You would have to add the admin again.

我怀疑截断 ie admin_* 表是否有用。如果您遵循上述唯一有价值的表格列表，就可以完成。您将不得不再次添加管理员。

Didn't check any further table. Just stumbled over the first 3 tables of my installation.

没有检查任何进一步的表。刚刚偶然发现了我安装的前 3 个表。