「SQL優化」varchar字符串按照int整型排序的實現方案及性能對比

「SQL小貼士」Mysql將varchar字符串按照int整型排序的實現方案和性能對比

前言

「SQL優化」varchar字符串按照int整型排序的實現方案及性能對比

在設計表的時候將用戶的“會員卡號”創建為varchar類型,實際上用戶的卡號是有規則的數字類型。這樣的設計在使用過程中並沒有出現問題,但是最近要根據“會員開好”進行排序的時候發現排序的結果有問題,具體信息如下:

<code>SQL示例:select * from user order by member_no desc limit 100;
查詢結果:
+-----------+
| member_no |
+-----------+
| 9000      | 
| 8000      | 
| 7000      | 
| 60000     | 
| 50000     | 
| 4000      | 
| 3000      | 
| 2000      | 
| 1000      | 
+-----------+/<code>

通過查詢的結果發現並不是我們想想中根據數字的大小按照倒敘展示,由此發現SQL中varchar類型要進行格式化為數字類型才能達到目標。

方案一:手動轉換類型

手動轉換類型常用做法有兩種:

SQL示例1:select * from user order by member_no+0 desc limit 100; // 補0

SQL示例2:select * from user order by member_no*1 desc limit 100; // 乘1

經測試可以達到預期效果。

方案二:函數CAST/CONVERT

SQL示例1:select * from user order by CAST(member_no as SIGNED) desc desc limit 100; // CAST

SQL示例2:select * from user order by CONVERT(member_no,SIGNED) desc limit 100; // CONVERT

經測試可以達到預期效果。

性能對比

那這幾種方法的性能有無差別呢?我來測試一下.....

第一步:準備數據

「SQL優化」varchar字符串按照int整型排序的實現方案及性能對比

如上圖所示,準備了字段20+,數量600萬+的測試數據。

第二步:依次執行SQL

<code>+-----------++-----------+
| member_no |
+-----------+
| 9000      | 
+-----------+/<code>

由此可見函數的寫法要比手動轉換的寫法耗時要少,兩種函數(CAST、CONVERT)耗時接近。


分享到:


相關文章: