“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;

注意no 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.

更新:我在 2009 年编写的这个演示是为 MyISAM 编写的.InnoDB 按索引顺序返回行，除非您使用 ORDER BY.这进一步证明了答案开始时的观点，即默认顺序取决于实现.使用不同的存储引擎意味着不同的实现.

UPDATE: This demonstration I wrote in 2009 was for MyISAM. InnoDB returns rows in index order unless you use ORDER BY. This is further evidence of the point at the start of the answer that the default order depends on the implementation. Using a different storage engine means a different implementation.

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