SQL订单字符串作为数字 [英] SQL order string as number

问题描述

我将数字另存为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.

如果无法执行此操作，则使用

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

BTW 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订单字符串作为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！