"SELECT * FROM table_name;"的MySQL行顺序是什么? [英] What is MySQL row order for "SELECT * FROM table_name;"?

问题描述

假定将以下查询发送到MySQL数据库:

Assume that the following query is issued to a MySQL database:

SELECT * FROM table_name;

请注意，给出了否 ORDER BY子句.

Note that no ORDER BY clause is given.

我的问题是:

MySQL是否保证结果集行的顺序?

Does MySQL give any guarantees to which order the result set rows will be given?

更具体地说，我可以假定行将按插入顺序返回吗?，这与将行插入表中的顺序相同.

More specifically, can I assume that the rows will be returned in insertion order?, that is the same order in which the rows were inserted into the table.

推荐答案

不，没有保证.除非您使用ORDER BY子句指定顺序，否则该顺序完全取决于内部实现细节. IE.对于RDBMS引擎最方便的地方.

No, there are no guarantees. Unless you specify an order using an ORDER BY clause, the order is totally dependent on internal implementation details. I.e. whatever is most convenient for the RDBMS engine.

在实践中，行可能会以其原始插入顺序(或更准确地说，行在物理存储中的存在顺序)返回，但您不应依赖于此.如果您将应用程序移植到其他品牌的RDBMS，或者即使升级到可能实现存储方式不同的MySQL的较新版本，行也可能以其他顺序返回.

In practice, the rows might be returned in their original insertion order (or more accurately the order the rows exist in physical storage), but you should not depend on this. If you port your app to another brand of RDBMS, or even if you upgrade to a newer version of MySQL that may implement storage differently, the rows could come back in some other order.

对于所有符合SQL的RDBMS，后一点都是正确的.

The latter point is true for any SQL-compliant RDBMS.

这里展示的是行在存储中的顺序与它们创建的顺序的含义:

Here's a demonstration of what I mean by the order the rows exist in storage, versus the order they were created:

CREATE TABLE foo (id SERIAL PRIMARY KEY, bar CHAR(10));

-- create rows with id 1 through 10
INSERT INTO foo (bar) VALUES
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing');

DELETE FROM foo WHERE id BETWEEN 4 AND 7;

+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
+----+---------+

所以现在我们有六行.此时的存储区在第3行和第8行之间有一个间隙，删除中间行后就剩下了.删除行不会对这些间隙进行碎片整理.

So now we have six rows. The storage at this point contains a gap between rows 3 and 8, left after deleting the middle rows. Deleting rows does not defragment these gaps.

-- create rows with id 11 through 20 
INSERT INTO foo (bar) VALUES
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing');

SELECT * FROM foo;

+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
| 14 | testing |
| 13 | testing |
| 12 | testing |
| 11 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
| 15 | testing |
| 16 | testing |
| 17 | testing |
| 18 | testing |
| 19 | testing |
| 20 | testing |
+----+---------+

在将新行追加到表末尾之前，请注意MySQL如何重新使用通过删除行打开的空格.还要注意，第11至14行以相反的顺序插入到这些空间中，从末尾向后填充.

Notice how MySQL has re-used the spaces opened by deleting rows, before appending new rows to the end of the table. Also notice that rows 11 through 14 were inserted in these spaces in reverse order, filling from the end backwards.

因此，行的存储顺序与插入顺序不完全相同.

Therefore the order the rows are stored is not exactly the order in which they were inserted.

这篇关于"SELECT * FROM table_name;"的MySQL行顺序是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！