mysql> select count(9),floor(rand(0)*2) as a from information_schema.tables group by a order by null;
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'
mysql>
上面这句话的先后顺序是什么?是先order by 还是group by 还是count(9) ?mysql> select count(9),floor(rand(0)*2) as a from information_schema.tables group by a order by null;
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'
mysql> select count(9),floor(rand(0)*2) as a from mysql.user group by a order by null;
+----------+---+
| count(9) | a |
+----------+---+
| 2 | 1 |
+----------+---+
1 row in set (0.00 sec)mysql> grant all on *.* to root@'192.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user,password from mysql.user;
+-------------+------+-------------------------------------------+
| host | user | password |
+-------------+------+-------------------------------------------+
| 127.% | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 127.0.0.1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 192.% | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 192.168.% | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 192.168.0.% | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-------------+------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> select count(9),floor(rand(0)*4) as a from mysql.user group by a order by null;
ERROR 1062 (23000): Duplicate entry '2' for key 'group_key'
为什么mysql.user表记录条数大于3,才报错加了group_concat后为什么就不报错了?
mysql> select count(9),floor(rand(0)*4) as a,group_concat(user) from mysql.user group by a order by null;
+----------+---+---------------------+
| count(9) | a | group_concat(user) |
+----------+---+---------------------+
| 1 | 0 | root |
| 1 | 1 | root |
| 4 | 2 | root,root,root,root |
+----------+---+---------------------+
3 rows in set (0.03 sec)mysql>
2.group by 先于order by
step2:count(*)
step3:order by