MySQL字符串按照數(shù)值排序方式

更新時間：2023年08月25日 09:01:14 作者：JustDI-CM

這篇文章主要介紹了MySQL字符串按照數(shù)值排序方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

MySQL字符串按照數(shù)值排序

1.需求

MySQL 有個表字段設計的時候，設置為了 varchar 類型，存取的卻是數(shù)值，后續(xù)有業(yè)務需要按照此字段排序，獲取最大值。

按照最原始的排序語句為：

SELECT * FROM test_21 a ORDER BY a.testId desc;

結果為：

這個并不是想要的結果。

2.方法

改造一下

1）與 int 進行計算

SELECT * FROM test_21 a ORDER BY a.testId+0 desc;
SELECT * FROM test_21 a ORDER BY a.testId-2 desc;
SELECT * FROM test_21 a ORDER BY a.testId*1 desc;
SELECT * FROM test_21 a ORDER BY a.testId/4 desc;

MySQL 的 varchar 與 int 計算時，會轉換為 int。

以上4個 sql 的執(zhí)行結果都為

2）cast() 或 convert()

SELECT * FROM test_21 a ORDER BY cast(a.testId as SIGNED) desc;
SELECT * FROM test_21 a ORDER BY convert(a.testId,SIGNED) desc;

這兩個函數(shù)都是用于格式轉換的，二者沒什么區(qū)別，

語法為：

CAST(expr AS type)
CONVERT(expr,type)

支持的類型有

值	說明
DATE	將expr轉換成’YYYY-MM-DD’格式
DATETIME	將expr轉換成’YYYY-MM-DD HH：MM：SS’格式
TIME	將expr轉換成’HH：MM：SS’格式
CHAR	將expr轉換成CHAR(固定長度的字符串)格式
SIGNED	將expr轉換成INT(有符號的整數(shù))格式
UNSIGNED	將expr轉換成INT(無符號的整數(shù))格式
DECIMAL	將expr轉換成FLOAT(浮點數(shù))格式
BINARY	將expr轉換成二進制格式

結果都為：

MySQL按字符串數(shù)字排序處理

公司不知是誰設計的表菜單排序字段竟然設置成了字符串。

CREATE TABLE `s_menu` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PARENT_ID` int(11) DEFAULT NULL,
  `ORDER` varchar(5) DEFAULT NULL,
  `MENU_NAME` varchar(255) DEFAULT NULL,
  `MENU_URL` varchar(255) DEFAULT NULL,
  `MENU_ICON` varchar(100) DEFAULT NULL,
  `SORT_NUM` int(2) DEFAULT NULL,
  `IS_SHOW` int(2) DEFAULT NULL,
  `PERMISSION` varchar(100) DEFAULT NULL,
  `LOGIN_TYPE` int(11) DEFAULT NULL COMMENT '0:WEB,1:PC,2:APP',
  `CREATE_BY` int(11) DEFAULT NULL,
  `CREATE_DATE` date DEFAULT NULL,
  `MEMO` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=utf8 COMMENT='菜單';

為了減少代碼的改動量

SELECT
  `ID`,
  `PARENT_ID` ,
  `ORDER` ,
  `MENU_NAME`,
  `MENU_URL` ,
  `MENU_ICON` ,
  `SORT_NUM` ,
  `IS_SHOW` ,
  `PERMISSION` ,
  `LOGIN_TYPE` ,
  `CREATE_BY` ,
  `CREATE_DATE`,
  `MEMO` 
FROM
    s_menu
ORDER BY
      (s_menu.ORDER+0) ASC

將字符的數(shù)字轉成數(shù)字，比如"123"轉成123可以直接用字符數(shù)字+0來實現(xiàn)

也可以使用 cast(str as SIGNED)

SELECT
  `ID`,
  `PARENT_ID` ,
  `ORDER` ,
  `MENU_NAME`,
  `MENU_URL` ,
  `MENU_ICON` ,
  `SORT_NUM` ,
  `IS_SHOW` ,
  `PERMISSION` ,
  `LOGIN_TYPE` ,
  `CREATE_BY` ,
  `CREATE_DATE`,
  `MEMO` 
FROM
    s_menu
ORDER BY
      cast (s_menu.ORDER as SIGNED) ASC