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.

如果你不能这样做，那么将你的列值转换为 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 订单字符串作为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！