Answer

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order – or even in a consistent order – unless you query your data with an ORDER BY clause.

Counter-Example

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(100) DEFAULT NULL,
  `age` char(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

创建一个测试数据库

INSERT INTO test VALUES(NULL,'张三','5');
INSERT INTO test VALUES(NULL,'李四','15');
INSERT INTO test VALUES(NULL,'王五','5');
INSERT INTO test VALUES(NULL,'赵信','15');
INSERT INTO test VALUES(NULL,'德玛','20');
INSERT INTO test VALUES(NULL,'皇子','5');
INSERT INTO test VALUES(NULL,'木木','17');
INSERT INTO test VALUES(NULL,'好汉','22');
INSERT INTO test VALUES(NULL,'水浒','18');
INSERT INTO test VALUES(NULL,'小芳','17');
INSERT INTO test VALUES(NULL,'老王','5');

按照正常的主键递增的顺序插入一些数据，然后查询：

mysql> SELECT * FROM test LIMIT 5;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   | 5    |
|  2 | 李四   | 15   |
|  3 | 王五   | 5    |
|  4 | 赵信   | 15   |
|  5 | 德玛   | 20   |
+----+--------+------+
5 rows in set (0.00 sec)

现在我们只查询两个字段（id 和 age）：

mysql> select id,age from test limit 5;
+----+------+
| id | age  |
+----+------+
|  2 | 15   |
|  4 | 15   |
|  7 | 17   |
| 10 | 17   |
|  9 | 18   |
+----+------+
5 rows in set (0.00 sec)

我们来分析一下，这两个SQL 是怎么被执行的：

mysql> EXPLAIN SELECT * FROM test LIMIT 5;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN select id,age from test limit 5;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | age  | 16      | NULL |   11 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在执行第一个 SQL时：

type 为 ALL：说明进行了 sequential scan （没有使用索引）

在执行第二个 SQL 时：

type 为 index，key 为 age：说明使用到了索引表。因此，查询出的结果顺序也和索引表中的顺序有关

Edvidence

SQL-92 Spec

If an <order by clause> is not specified, then the ordering of the rows of Q is implementation-dependent.

PostgreSQL

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

The ORDER BY clause specifies the sort order:

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

MySQL

Do not depend on order when ORDER BY is missing.
Always specify ORDER BY if you want a particular order – in some situations the engine can eliminate the ORDER BY because of how it does some other step.

【MySQL】查询结果顺序问题

Answer

Counter-Example

Edvidence

SQL-92 Spec

PostgreSQL

MySQL

Reference

FEATURED TAGS

FRIENDS

TOC