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 显式 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 顺序字符串作为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！