MySQL中的默认排序(ALTER TABLE ... ORDER BY ...;) [英] Default sort-ordering in MySQL (ALTER TABLE ... ORDER BY ...;)

问题描述

假定通过执行以下操作来更改MySQL表(ISAM)的默认顺序:

Assume that the default ordering of a MySQL-table (ISAM) is changed by executing:

ALTER TABLE tablename ORDER BY columnname ASC;

从现在开始，我保证以假设我的查询中未指定"ORDER BY"的方式按列名ASC"的顺序获取从表中检索到的记录.在哪里...限制10;)?

From now on, am I guaranteed to obtain records retrieved from the table in the order of "columnname ASC" assuming no "ORDER BY" is specified in my queries (i.e. "SELECT * FROM tablename WHERE ... LIMIT 10;")?

我应该注意什么极端情况吗?

Are there any corner-cases that I should be aware of?

更新#1:非常感谢Quassnoi正确地指出INSERT和DELETE弄乱了顺序.这使我想到以下额外的问题:

Update #1: Thanks a lot to Quassnoi who correctly pointed out that INSERTs and DELETEs messes up the ordering. This leads me to the following to extra questions:

• 那UPDATEs又如何呢?假设没有对表进行INSERT或DELETE操作，而仅对更新进行了操作-排序顺序会保持不变吗?
• 假设已完成INSERT和DELETE-如何再次重建"排序，每天说一次(在这种情况下，表格每天仅更改一次，因此在完成更改后每天重新生成表格应该还是可以的！ ). REPAIR TABLE是否可以修复它，还是必须再次添加ALTER TABLE ... ORDER BY?

推荐答案

来自

请注意，表在插入和删除后不会按此顺序保留

Note that the table does not remain in this order after inserts and deletes

实际上，如果您向该表发出SELECT ... ORDER BY，则ALTER TABLE的选项不会使您省去filesort，而是可以使filesort更快.

Actually, if you issue SELECT ... ORDER BY to this table, the option to ALTER TABLE won't spare you of filesort, but instead make filesort much faster.

对已排序的集合进行排序等同于浏览该集合以确保一切正常.

Sorting an already ordered set is equivalent to browsing this set to ensure everything is OK.

那UPDATE呢?假设没有对表进行INSERT或DELETE操作，而仅对更新进行了操作-排序顺序是否完整?

What about UPDATEs? Assume that no INSERTs or DELETEs are made to the table, but only updates - will the sort order be intact?

如果您的表不包含任何动态字段(例如VARCHAR或'BLOB')，则很有可能 MyISAM在更新时不会移动它.

If your table does not contain any dynamic fields (like VARCHAR or 'BLOB'), then most probably MyISAM will not move it when updating.

但是，如果我正在建造核电站或获得报酬的东西，我将不会依赖这种行为.

假设已完成INSERT和DELETE操作-如何再次重建"排序，每天说一次(在这种情况下，表格每天只更改一次，因此在更改完成后每天重建它应该还是可以的！ ). REPAIR TABLE是否可以修复它，还是必须再次添加ALTER TABLE ... ORDER BY?

Assume that INSERTs and DELETEs are made - how do I "rebuild" the sorting again, say once a day (in this specific case the table only changes daily, so rebuilding it daily after the changes are done should still be OK!). Does REPAIR TABLE fix it, or must add do ALTER TABLE ... ORDER BY again?

您需要执行ALTER TABLE ... ORDER BY.

REPAIR只是修复了损坏表的物理结构.

REPAIR just fixes the physical structure of a corrupted table.

这篇关于MySQL中的默认排序(ALTER TABLE ... ORDER BY ...;)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！