order by 理解 请大虾们分别对以下没戏每项结果集进行分析

Question

关于user表
mysql> select * from user;
+--------+--------------------+
| userid | username           |
+--------+--------------------+
|      1 | aaaaaaaaaaaaaaaaaa |
|      2 | 螟ｧ譏ｯ荳ｪ             |
|      3 | 譏ｯ蠕ｷ蝗ｽ             |
|      4 |                    |
|      5 | 莠懶ｽ難ｽ・            |
|      6 | 螢ｫ螟ｧ螟ｫ             |
|      7 | d                  |
|      8 | 螳俶婿               |
|      9 | ?                  |
+--------+--------------------+
9 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
mysql> select * from user order by rand()*1000000000;
+--------+--------------------+
| userid | username           |
+--------+--------------------+
|      3 | 譏ｯ蠕ｷ蝗ｽ             |
|      6 | 螢ｫ螟ｧ螟ｫ             |
|      8 | 螳俶婿               |
|      5 | 莠懶ｽ難ｽ・            |
|      2 | 螟ｧ譏ｯ荳ｪ             |
|      9 | ?                  |
|      1 | aaaaaaaaaaaaaaaaaa |
|      4 |                    |
|      7 | d                  |
+--------+--------------------+
9 rows in set (0.00 sec)mysql> select * from user order by rand()*1000000000;
+--------+--------------------+
| userid | username           |
+--------+--------------------+
|      4 |                    |
|      1 | aaaaaaaaaaaaaaaaaa |
|      7 | d                  |
|      9 | ?                  |
|      6 | 螢ｫ螟ｧ螟ｫ             |
|      3 | 譏ｯ蠕ｷ蝗ｽ             |
|      5 | 莠懶ｽ難ｽ・            |
|      8 | 螳俶婿               |
|      2 | 螟ｧ譏ｯ荳ｪ             |
+--------+--------------------+
9 rows in set (0.02 sec)mysql> select * from user order by rand()*1000000000;
+--------+--------------------+
| userid | username           |
+--------+--------------------+
|      2 | 螟ｧ譏ｯ荳ｪ             |
|      6 | 螢ｫ螟ｧ螟ｫ             |
|      8 | 螳俶婿               |
|      1 | aaaaaaaaaaaaaaaaaa |
|      9 | ?                  |
|      5 | 莠懶ｽ難ｽ・            |
|      3 | 譏ｯ蠕ｷ蝗ｽ             |
|      7 | d                  |
|      4 |                    |
+--------+--------------------+
9 rows in set (0.00 sec)mysql> select * from user order by rand()*1000000000;
+--------+--------------------+
| userid | username           |
+--------+--------------------+
|      9 | ?                  |
|      4 |                    |
|      8 | 螳俶婿               |
|      5 | 莠懶ｽ難ｽ・            |
|      6 | 螢ｫ螟ｧ螟ｫ             |
|      1 | aaaaaaaaaaaaaaaaaa |
|      2 | 螟ｧ譏ｯ荳ｪ             |
|      7 | d                  |
|      3 | 譏ｯ蠕ｷ蝗ｽ             |
+--------+--------------------+
9 rows in set (0.00 sec)mysql> select * from user order by rand();
+--------+--------------------+
| userid | username           |
+--------+--------------------+
|      7 | d                  |
|      2 | 螟ｧ譏ｯ荳ｪ             |
|      9 | ?                  |
|      3 | 譏ｯ蠕ｷ蝗ｽ             |
|      5 | 莠懶ｽ難ｽ・            |
|      6 | 螢ｫ螟ｧ螟ｫ             |
|      4 |                    |
|      1 | aaaaaaaaaaaaaaaaaa |
|      8 | 螳俶婿               |
+--------+--------------------+
9 rows in set (0.00 sec)mysql> select * from user order by 2;
+--------+--------------------+
| userid | username           |
+--------+--------------------+
|      4 |                    |
|      9 | ?                  |
|      1 | aaaaaaaaaaaaaaaaaa |
|      7 | d                  |
|      5 | 莠懶ｽ難ｽ・            |
|      6 | 螢ｫ螟ｧ螟ｫ             |
|      2 | 螟ｧ譏ｯ荳ｪ             |
|      8 | 螳俶婿               |
|      3 | 譏ｯ蠕ｷ蝗ｽ             |
+--------+--------------------+
9 rows in set (0.00 sec)mysql> select * from user order by 132456.12345;
+--------+--------------------+
| userid | username           |
+--------+--------------------+
|      1 | aaaaaaaaaaaaaaaaaa |
|      2 | 螟ｧ譏ｯ荳ｪ             |
|      3 | 譏ｯ蠕ｷ蝗ｽ             |
|      4 |                    |
|      5 | 莠懶ｽ難ｽ・            |
|      6 | 螢ｫ螟ｧ螟ｫ             |
|      7 | d                  |
|      8 | 螳俶婿               |
|      9 | ?                  |
+--------+--------------------+
9 rows in set (0.00 sec)mysql> select * from user order by 132456;
ERROR 1054 (42S22): Unknown column '132456' in 'order clause'

Answer 1

[ORDER BY {col_name | expr | position}被选择的用于输出的列可以使用列名称、列别名或列位置被引用到ORDER BY和GROUP BY子句中。列位置为整数，从1开始：

Answer 2

那版主解释下我上面的order by rand()
运行原理吧mysql> select rand();
+-------------------+
| rand()            |
+-------------------+
| 0.832840707928725 |
+-------------------+
1 row in set (0.00 sec)

Answer 3

order by rand()这种是以表达式结果来排序。
在ORDER BY语句中，不能使用一个带有RAND()值的列，原因是 ORDER BY 会计算列的多重时间。然而，可按照如下的随机顺序检索数据行： mysql> SELECT * FROM tbl_name ORDER BY RAND();ORDER BY RAND()同 LIMIT 的结合从一组列中选择随机样本很有用：

Answer 4

原因是 ORDER BY 会计算列的多重时间。
--------------------------------
不是很理解
查询应该是先查询结果集 然后在order by 排序
如果是这样的话order by的列就是个定值而不是随机制如果select * from user where id=ceil(rand()*10);
那么它是一行一行匹配如果有索引也是匹配多行

Answer 5

是随机RAND 函数是每行计算，与其它函数不同。

Answer 6

个人意见:SELECT * FROM tbl_name ORDER BY RAND();
应该等价于
SELECT *,RAND() as px FROM tbl_name ORDER BY px;

Answer 7

但如果是一个固定的数，order by 132456 就是按照第132456列进行排序，显然你的列数没有这么多

Answer 8

本帖最后由 ACMAIN_CHM 于 2009-09-17 12:20:47 编辑

  

Answer 9

谢谢ACMAIN_CHM的分享！不知道用视图查询会这么样，试试这个select id,crand
from(
select id,rand() as crand from t2) t
order by crand;

Answer 10

但是他order by 任何类型的数据都可以它底层是怎么实现的呢

Answer 11

底层的东西不了解，但是可以按照任何类型的数据来排序这个不难理解就像一群人要排队，
排队的先后顺序我们可以按照他们的性别(bit 0 or 1)、名字(varchar)、身高(numeric)等等进行排序

Answer 12

像版主说的我就不明白了
mysql> select * from user order by rand();
+--------+-----------+
| userid | username  |
+--------+-----------+
|      7 | d         |
|      8 | 螳俶婿      |
|      2 | 螟ｧ譏ｯ荳ｪ    |
|      4 |           |
|      9 | ?         |
|      6 | 螢ｫ螟ｧ螟ｫ    |
|      5 | 莠懶ｽ難ｽ・   |
|      3 | 譏ｯ蠕ｷ蝗ｽ    |
+--------+-----------+
8 rows in set (0.05 sec)
结果集如上 其也是随机抽取一列的实现之一 但是如果每行计算的话 结果集是如何排序的 

Answer 13

读取所有的行，对每行生成这个RAND，然后再对这个临时结果集进行排序。

Answer 14

mysql> explain select * from t2 order by rand();
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |   11 | Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)

Answer 15

对比一下下面的结果，就比较容易理解了。mysql> explain select * from t2 order by rand();
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |   11 | Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)mysql> explain select * from t2 order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |   11 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql> explain select id,rand() from t2 order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |   11 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql>

Answer 16

版主的意思可能是，select *, rand() from tbl; 的执行过程中，每取出一条结果的同时计算一个随机数！
而max，min等函数则是列结果出来之后才计算！这个可以楼主按照 《understanding mysql internals》中对mysql的调试方法来看看到底那个值是怎么出来的！

Answer 17

明白了 
rand()相当于生成一个伪列 然后按照伪劣进行排序
mysql> select username ,userid as Vatural from user order by Vatural;
+-----------+---------+
| username  | Vatural |
+-----------+---------+
| 螟ｧ譏ｯ荳ｪ    |       2 |
| 譏ｯ蠕ｷ蝗ｽ    |       3 |
|           |       4 |
| 莠懶ｽ難ｽ・   |       5 |
| 螢ｫ螟ｧ螟ｫ    |       6 |
| d         |       7 |
| 螳俶婿      |       8 |
| ?         |       9 |
+-----------+---------+
8 rows in set (0.00 sec)mysql> select username ,rand() as Vatural from user order by Vatural;
+-----------+--------------------+
| username  | Vatural            |
+-----------+--------------------+
| 螟ｧ譏ｯ荳ｪ    | 0.0555073358635486 |
| 莠懶ｽ難ｽ・   |  0.134616873352432 |
| ?         |  0.185337211177998 |
| 螳俶婿      |  0.270653398028252 |
|           |  0.371484097439315 |
| d         |  0.389309936565636 |
| 螢ｫ螟ｧ螟ｫ    |  0.558632105177857 |
| 譏ｯ蠕ｷ蝗ｽ    |  0.907601214859263 |
+-----------+--------------------+
8 rows in set (0.00 sec)

Answer 18

sqlcli> select * from t1 order by 1;
+------+
| a    |
+------+
|    1 |
|   10 |
+------+
2 rows in set (0.00 sec)sqlcli> select * from t1 order by 2;
ERROR 1054 (42S22): Unknown column '2' in 'order clause'
sqlcli> select * from t1 order by '2';
+------+
| a    |
+------+
|    1 |
|   10 |
+------+
2 rows in set (0.01 sec)order by 2 = order by 第二个字段