查询返回的默认顺序

按照sql标准，select如果不加order by子句，返回结果的顺序是不可靠的。

如果你希望保证顺序，必须要加上order by子句。

见下面的例子：

innodb的情况

test@127.0.0.1>CREATE TABLE t (
   ->   ID INT NOT NULL PRIMARY KEY,
   ->   PID INT,
   ->   FOREIGN KEY (PID) REFERENCES t(ID)
   -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

test@127.0.0.1>INSERT INTO t VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)

test@127.0.0.1>INSERT INTO t VALUES (2, NULL);
Query OK, 1 row affected (0.00 sec)

test@127.0.0.1>select * from t;
+----+------+
| ID | PID  |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

test@127.0.0.1>UPDATE t SET PID  = 2 WHERE ID = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

test@127.0.0.1>select * from t;
+----+------+
| ID | PID  |
+----+------+
|  2 | NULL |
|  1 |    2 |
+----+------+
2 rows in set (0.00 sec)

myisam的情况

test@127.0.0.1>create table t (a int primary key, b int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

test@127.0.0.1>insert into t values (1,2),(2,3),(3,4);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

test@127.0.0.1>delete from t where a = 2;
Query OK, 1 row affected (0.00 sec)

test@127.0.0.1>insert into t values (4,5);
Query OK, 1 row affected (0.00 sec)

test@127.0.0.1>select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 4 |    5 |
| 3 |    4 |
+---+------+
3 rows in set (0.00 sec)