SQL 订单字符串作为数字

SQL order string as number(SQL 订单字符串作为数字)
本文介绍了SQL 订单字符串作为数字的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我将数字保存为 VARCHAR 到 MySQL 数据库.由于某些其他情况,我无法将它们设为 INT.

I have numbers saved as VARCHAR to a MySQL database. I can not make them INT due to some other depending circumstances.

排序时将它们视为字符而不是数字.

It is taking them as character not as number while sorting.

在数据库中我有

1 2 3 4 5 6 7 8 9 10...

在我的页面上,它显示了这样的有序列表:

On my page it shows ordered list like this:

1 10 2 3 4 5 6 7 8 9

如何让它显示为按数字升序排列?

How can I make it appear ordered by numbers ascending?

推荐答案

如果可能的话,如果您只存储数字,则应该将列的数据类型更改为数字.

If possible you should change the data type of the column to a number if you only store numbers anyway.

如果你不能这样做,那么将你的列值转换为 integer explicitly with

If you can't do that then cast your column value to an integer explicitly with

select col from yourtable
order by cast(col as unsigned)

隐式,例如使用强制转换为数字的数学运算

or implicitly for instance with a mathematical operation which forces a conversion to number

select col from yourtable
order by col + 0

顺便说一句,MySQL 从左到右转换字符串.例子:

BTW MySQL converts strings from left to right. Examples:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

这篇关于SQL 订单字符串作为数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)