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

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

示例数据:area_gdp

idareagdp
1北京1200
2上海6100
3广州6000
4深圳980
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

idareagdp
1北京1200
2上海6100
3广州6000
4深圳980

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 表示有符号,可以表示小数

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注