MySql遇到varchar字段数字字符串排序问题

项目中遇到的问题,一个笔试试卷成绩查询最高分和最低分,简单的对分数字段排序,一直是正常的。

出bug了

就这个简单的功能,突然有一天,客户发现很奇怪的是最高分比最低分还低。
这明显是有问题的:

mysql中varchar数字排序

数据库大概是这样的:

id name score
1 小明 32
2 小红 8
3 小王 40
4 小武 16

直接排序:

SELECT * FROM `exam_score` ORDER BY score DESC;

输出结果:

id name score
2 小红 8
3 小王 40
1 小明 32
4 小武 16

一个存储成绩的表的分数score字段原本为int类型被修改为varchar类型后,原本正常的最高分最低分输出,变得不正确了。

原因

回溯找原因,原来这个score字段在当初是int字段,后来又一次小伙伴在笔试题库里加入了0.5分值的题目,为了兼容这个情况,很粗暴的把成绩字段也直接改为了varchar类型,而这个根据score字段排序的代码没有一起修改,那遇到这种情况,如何能正确的排序呢?

正确的使用varchar字段里的数字排序:
借助函数CONVERT(value as type),其中的type可选有如下几种类型,按需使用:

  • DECIMAL:浮点数
  • SIGNED:整数
  • UNSIGNED:无符号整数

由于这里score可能出现小数,所以应该选用DECIMAL

SELECT * FROM `exam_score` ORDER BY CONVERT(score,DECIMAL) DESC;

将字段转化为DECIMAL类型再排序,这样就可以了。

mysql存储小数更好的办法

这个问题其实是使用mysql存储小数的问题,直接使用字符串执行效率会降低,推荐方案:

  • 使用乘以10的倍数来存储,比如价格以分(0.01元)为单位,乘以100后使用整数存储,不会丢失精度,也不会出现类似的排序问题。
  • 使用decimal类型的方式存储。
  • 将小数和整数部分分开存储。

参考

MySql遇到varchar字段数字字符串排序问题

原文链接:https://beltxman.com/3285.html,若无特殊说明本站内容为行星带原创,未经同意禁止转载!

更多精彩,敬请关注本博微信公众号:hsu1943

MySql遇到varchar字段数字字符串排序问题”上有 2 条评论;

发表评论

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

Scroll to top