MySQL order by 排序結果不正確
MySQL order by 排序結果不正確
新建一張測試表:
CREATE TABLE `tb1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`a` decimal(19,2) NOT NULL,
`acid` bigint(20) NOT NULL,
`prid` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_prid` (`prid`),
KEY `idx_acid` (`acid`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
欄位 a 沒有索引,插入測試資料:
INSERT INTO `tb1` (`id`, `a`, `acid`, `prid`)
VALUES (1,2.00,3,2),(2,3.00,3,2),(3,4.00,2,3),(4,5.00,2,3),(5,6.00,2,3),(6,8.00,2,3),(7,10.00,2,3),(8,12.00,2,3),(9,16.00,2,3),(10,20.00,2,3),(11,6.00,2,4),(12,8.00,2,4),(13,10.00,2,4),(14,12.00,2,4),(15,5.00,2,2),(16,6.00,2,2);
檢視錶資料:
([yoon]> select * from tb1;
+----+-------+------+------+
| id | a | acid | prid |
+----+-------+------+------+
| 1 | 2.00 | 3 | 2 |
| 2 | 3.00 | 3 | 2 |
| 3 | 4.00 | 2 | 3 |
| 4 | 5.00 | 2 | 3 |
| 5 | 6.00 | 2 | 3 |
| 6 | 8.00 | 2 | 3 |
| 7 | 10.00 | 2 | 3 |
| 8 | 12.00 | 2 | 3 |
| 9 | 16.00 | 2 | 3 |
| 10 | 20.00 | 2 | 3 |
| 11 | 6.00 | 2 | 4 |
| 12 | 8.00 | 2 | 4 |
| 13 | 10.00 | 2 | 4 |
| 14 | 12.00 | 2 | 4 |
| 15 | 5.00 | 2 | 2 |
| 16 | 6.00 | 2 | 2 |
+----+-------+------+------+
根據非索引欄位且有重複資料的欄位 a 進行 order by 排序:
([yoon]> select * from tb1 order by a desc ;
+----+-------+------+------+
| id | a | acid | prid |
+----+-------+------+------+
| 10 | 20.00 | 2 | 3 |
| 9 | 16.00 | 2 | 3 |
| 14 | 12.00 | 2 | 4 |
| 8 | 12.00 | 2 | 3 |
| 13 | 10.00 | 2 | 4 |
| 7 | 10.00 | 2 | 3 |
| 12 | 8.00 | 2 | 4 |
| 6 | 8.00 | 2 | 3 |
| 11 | 6.00 | 2 | 4 |
| 16 | 6.00 | 2 | 2 |
| 5 | 6.00 | 2 | 3 |
| 4 | 5.00 | 2 | 3 |
| 15 | 5.00 | 2 | 2 |
| 3 | 4.00 | 2 | 3 |
| 2 | 3.00 | 3 | 2 |
| 1 | 2.00 | 3 | 2 |
+----+-------+------+------+
order by 和 limit 一起使用:
([yoon]> select * from tb1 order by a desc limit 4;
+----+-------+------+------+
| id | a | acid | prid |
+----+-------+------+------+
| 10 | 20.00 | 2 | 3 |
| 9 | 16.00 | 2 | 3 |
| 14 | 12.00 | 2 | 4 |
| 8 | 12.00 | 2 | 3 |
+----+-------+------+------+
為 a 欄位新增索引:
([yoon]> alter table tb1 add index idx_a(a);
Query OK, 0 rows affected (0.05 sec)
([yoon]> select * from tb1 order by a desc limit 4;
+----+-------+------+------+
| id | a | acid | prid |
+----+-------+------+------+
| 10 | 20.00 | 2 | 3 |
| 9 | 16.00 | 2 | 3 |
| 14 | 12.00 | 2 | 4 |
| 8 | 12.00 | 2 | 3 |
+----+-------+------+------+
排序的時候再新增一個欄位 id :
([yoon]> select * from tb1 order by a desc,id desc limit 4;
+----+-------+------+------+
| id | a | acid | prid |
+----+-------+------+------+
| 10 | 20.00 | 2 | 3 |
| 9 | 16.00 | 2 | 3 |
| 14 | 12.00 | 2 | 4 |
| 8 | 12.00 | 2 | 3 |
+----+-------+------+------+
對於一個非唯一欄位,無論是否含有索引,結果集都是不確定的。如果業務邏輯對分頁或者order by結果集有比較高的嚴格要求 ,請記得利用唯一鍵排序。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28939273/viewspace-2683988/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【MySQL】order by 結果不準確的問題及解決MySql
- ORDER對查詢結果進行排序排序
- MYSQL order by排序與索引關係總結MySql排序索引
- MySQL Order BY 排序過程MySql排序
- 等於NULL的查詢條件導致查詢結果不正確Null
- MySQL 5.7 ORDER BY排序的優化MySql排序優化
- MySQL distinct 和 order by 排序混淆的替代方案MySql排序
- MySQL利用索引優化ORDER BY排序語句MySql索引優化排序
- MySql Order By 多個欄位 排序規則MySql排序
- mysql按照漢字拼音進行order by排序MySql排序
- MYSQL order by排序導致效率低小優化MySql排序優化
- order by改分組排序排序
- 年假計算居然不正確
- Elasticsearch聚合學習之四:結果排序Elasticsearch排序
- 程式中使用繫結變數,執行計劃不正確變數
- R排序sort、order、rank、arrange排序
- MySQL之order byMySql
- Mysql優化_ORDER BY和GROUP BY 的優化講解(單路排序和雙路排序)MySql優化排序
- MySQL:關於排序order by limit值不穩定的說明(1)MySql排序MIT
- MYSQL實現ORDER BY LIMIT的方法以及優先佇列(堆排序)MySqlMIT佇列排序
- mysql group by 取想要的結果MySql
- 【MYSQL 基準測試結果】MySql
- mysql匯出查詢結果MySql
- MySQL 之 ORDER BY FIELDMySql
- mysql order by 優化MySql優化
- Master Exam中答案不正確的題AST
- 【SQL 學習】排序問題之order by與索引排序SQL排序索引
- MySQL查詢結果匯出方式總結MySql
- mysql怎麼設定密碼都不正確的一個解決方法MySql密碼
- Laravel 根據 relation sum 結果排序的小技巧Laravel排序
- SQL Server對組合查詢結果排序方法SQLServer排序
- 9i下含有connect by會導致以關聯欄位為條件做查詢時,結果不正確的現象
- MySQL in UnionAll結果集的優化MySql優化
- MySQL的EXPLAIN命令結果詳解MySqlAI
- 關於order by中的資料排序排序
- 數字型別的不正確轉換漏洞型別
- percona server 日誌時間不正確Server
- mysql的order by和group byMySql