sid 是主键 order_no 是唯一索引谁能帮我解释一下3次查询结果 为什么不一样, 在3个sql 的排序规则都是什么mysql> select sid,order_no,order_status from oms_order limit 10;
+-----+----------------+--------------+
| sid | order_no | order_status |
+-----+----------------+--------------+
| 20 | 20100319000003 | 0 |
| 21 | 20100319000004 | 0 |
| 63 | 20100319000046 | -1 |
| 69 | 20100320000006 | 0 |
| 76 | 20100323000001 | 0 |
| 77 | 20100323000002 | 0 |
| 78 | 20100323000003 | 0 |
| 79 | 20100323000004 | 8 |
| 86 | 20100324000007 | -1 |
| 93 | 20100324000014 | -1 |
+-----+----------------+--------------+
10 rows in set (0.00 sec)mysql> select sid,order_status from oms_order limit 10;
+-----+--------------+
| sid | order_status |
+-----+--------------+
| 240 | -1 |
| 261 | -1 |
| 263 | -1 |
| 285 | -1 |
| 286 | -1 |
| 288 | -1 |
| 339 | -1 |
| 383 | -1 |
| 387 | -1 |
| 452 | -1 |
+-----+--------------+
10 rows in set (0.00 sec)mysql> select sid from oms_order limit 10;
+---------+
| sid |
+---------+
| 1171641 |
| 1171651 |
| 1171661 |
| 1171671 |
| 1171681 |
| 1171691 |
| 1171701 |
| 20 |
| 21 |
| 63 |
+---------+
10 rows in set (0.00 sec)
+-----+----------------+--------------+
| sid | order_no | order_status |
+-----+----------------+--------------+
| 20 | 20100319000003 | 0 |
| 21 | 20100319000004 | 0 |
| 63 | 20100319000046 | -1 |
| 69 | 20100320000006 | 0 |
| 76 | 20100323000001 | 0 |
| 77 | 20100323000002 | 0 |
| 78 | 20100323000003 | 0 |
| 79 | 20100323000004 | 8 |
| 86 | 20100324000007 | -1 |
| 93 | 20100324000014 | -1 |
+-----+----------------+--------------+
10 rows in set (0.00 sec)mysql> select sid,order_status from oms_order limit 10;
+-----+--------------+
| sid | order_status |
+-----+--------------+
| 240 | -1 |
| 261 | -1 |
| 263 | -1 |
| 285 | -1 |
| 286 | -1 |
| 288 | -1 |
| 339 | -1 |
| 383 | -1 |
| 387 | -1 |
| 452 | -1 |
+-----+--------------+
10 rows in set (0.00 sec)mysql> select sid from oms_order limit 10;
+---------+
| sid |
+---------+
| 1171641 |
| 1171651 |
| 1171661 |
| 1171671 |
| 1171681 |
| 1171691 |
| 1171701 |
| 20 |
| 21 |
| 63 |
+---------+
10 rows in set (0.00 sec)
解决方案 »
- 关于mysql触发器语句的问题
- mysql转换成excel
- Mysql数据库主键是uuid如何插入数据
- MySQL 时间函数问T,,,,,
- 查询和插入问题
- mysql存储图形文件的问题
- 有关流程图!
- 高分请教:sql如何实现动态港存的计算问题 (给出具体sql实现方法100分)
- 请大神指教这个MYSQL语句的错误,关于触发器的
- mysql创建视图时出现 #1349 - View's SELECT contains a subquery in the FROM clause
- mysql error 1042 can not get hostname for your address
- 求一段MYSQL搜索修改语句
这个肯定优先使用 sid 排序,再使用 order_no 排序select sid,order_status from oms_order limit 10;
sid 排序select sid from oms_order limit 10;
这个只有 sid,只好使用它排序了,看上去 sid 是字符型的数据,所以 1171701 在 20 前。
第二句 为什么和第一句结果不一样?
第三句 sid bigint(20) 但是下面的id 是今天用程序插入的 | 1171641 |
| 1171651 |
| 1171661 |
| 1171671 |
| 1171681 |
| 1171691 |
| 1171701 |
+----+-------------+-----------+-------+---------------+----------------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+----------------------+---------+------+---------+-------------+
| 1 | SIMPLE | oms_order | index | NULL | IND_order_mem_status | 13 | NULL | 1139498 | Using index |
+----+-------------+-----------+-------+---------------+----------------------+---------+------+---------+-------------+
1 row in set (0.00 sec)mysql>
mysql>
mysql> explain select sid,order_no,order_status from oms_order limit 10;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | oms_order | ALL | NULL | NULL | NULL | NULL | 1139498 | |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)mysql> explain select sid from oms_order limit 10;
+----+-------------+-----------+-------+---------------+--------------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+--------------------+---------+------+---------+-------------+
| 1 | SIMPLE | oms_order | index | NULL | IND_ORDER_DELIVERY | 9 | NULL | 1139498 | Using index |
+----+-------------+-----------+-------+---------------+--------------------+---------+------+---------+-------------+
1 row in set (0.00 sec)