56,687
社区成员
发帖
与我相关
我的任务
分享
关于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'
mysql> select id,rand() as crand from t2 order by crand;
+--------+--------------------+
| id | crand |
+--------+--------------------+
| 50000 | 0.059513117242151 |
| 80000 | 0.0837476030772064 |
| 40000 | 0.099545445385897 |
| 30000 | 0.146071380140326 |
| 20000 | 0.210270495349793 |
| 1 | 0.543954915873664 |
| 100000 | 0.600841207989344 |
| 70000 | 0.816107082940738 |
| 10000 | 0.968427042447428 |
| 90000 | 0.970416797297464 |
| 60000 | 0.998929280786709 |
+--------+--------------------+
11 rows in set (0.00 sec)
mysql>
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.832840707928725 |
+-------------------+
1 row in set (0.00 sec)
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)
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>
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)