MySQL 将字符串转换为数字类型并进行排序

冷不防 2022-11-12 02:49 401阅读 0赞

### 目录 ###

*  1. 方法一: \`字段值 + 0\`
 *  2. 方法二: 通过 \`case()\` 函数
 *  3. 方法三： 通过 \`CONVERT()\` 函数

需要对接第三方统计系统，并且第三方系统给的数据那真的是一团乱，害，都是泪呀，头发又感觉凉飕飕的；数据有毒，所有的 `小数`都是用 `varchar(20)` 保存的，现在有要对该数据进行排序并展示。

示例数据：`area_gdp 表`

select * from area_gdp ORDER BY gdp ASC
    
    # 查询结果如下
    1	北京		1200
    3	广州 	6000
    2	上海		6100
    4	深圳 	980
    
    SELECT * FROM area_gdp ORDER BY gdp DESC
    # 查询结果如下
    4	深圳		980
    2	上海		6100
    3	广州		6000
    1	北京		1200

结果与预想的不一样，其实 `MySQL` 没有想象中的那么智能，对于 `char` 或者是 `varchar` 类型的数据，只会当做字符串进行对待，所以会依次的对字符串中的每一个字符进行排序。

如果明确知道 `char` 或者是 `varchar` 里面保存的是数字类型，可以在排序的时候将字段转换成数字，并实现想要的排序功能；**这里提供三种方法**，如果有其他更好的可以互相交流一下。

示例数据：`area_gdp 表`

# 1. 方法一: `字段值 + 0` #

`MySql` 会根据上下文自动转换类型，这里会将被本来是字符类型的字段值以数值型返回，`如果字段存放的值为数值字符，则会转换为数值型返回，如果字段存放的值为字母或者汉字，则直接返回 0`。

# 示例
    
    SELECT 'abd'+0  # 结果为 0 
    SELECT 'abd5'+0  # 结果为 0 
    SELECT '5abd'+0  # 结果为 5
    SELECT '5abd5'+0 # 结果为 5
    SELECT '55'+0 # 结果为 55

# 解决上面的问题
    
    select * from area_gdp ORDER BY gdp+0 ASC
    
    # 查询结果如下
    4	深圳		980
    1	北京		1200
    3	广州		6000
    2	上海		6100
    
    select * from area_gdp ORDER BY gdp+0 DESC
    # 查询结果如下
    2	上海		6100
    3	广州		6000
    1	北京		1200
    4	深圳		980

# 2. 方法二: 通过 `case()` 函数 #

`CAST()` 函数，把一个字段的值转成另一个类型并输出。  
用法： `CAST ( 字段名 AS 转换的类型 )`

select * from area_gdp ORDER BY CAST(gdp AS SIGNED) ASC
    
    # 查询结果如下
    4	深圳		980
    1	北京		1200
    3	广州		6000
    2	上海		6100
    
    select * from area_gdp ORDER BY CAST(gdp AS SIGNED) DESC
    # 查询结果如下
    2	上海		6100
    3	广州		6000
    1	北京		1200
    4	深圳		980

>  *  `unsigned` 表示无符号，不能表示小数
>  *  `signed` 表示有符号，可以表示小数

# 3. 方法三： 通过 `CONVERT()` 函数 #

`CONVERT()` 函数，把一个字段的值转成另一个类型并输出。  
用法： `CONVERT ( 字段名 , 转换的类型 )`

select * from area_gdp ORDER BY CONVERT(gdp,SIGNED) ASC
    
    # 查询结果如下
    4	深圳		980
    1	北京		1200
    3	广州		6000
    2	上海		6100
    
    select * from area_gdp ORDER BY CONVERT(gdp,SIGNED) DESC
    # 查询结果如下
    2	上海		6100
    3	广州		6000
    1	北京		1200
    4	深圳		980

>  *  `unsigned` 表示无符号，不能表示小数
>  *  `signed` 表示有符号，可以表示小数

博客地址：[Roc’s Blog][Roc_s Blog]

[Roc_s Blog]: http://roc.havemail.cn/archives/1370.html