MySQL mysql将varchar字段作为整数排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1625166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql order varchar field as integer
提问by ntan
I have a varchar field in my table and I want to sort it. But I need to handle this field as integer. Meaning if sort as text the order is "19,2,20" but I want to get the right order "2,19,20".
我的表中有一个 varchar 字段，我想对其进行排序。但我需要将此字段作为整数处理。意思是如果按文本排序，顺序是“19,2,20”，但我想得到正确的顺序“2,19,20”。
Can anyone help me?
谁能帮我？
回答by yentsun
I somehow didn't manage to run the query with CAST. I was always getting Error Code: 1064 near "DECIMAL"(or other numeric type that I chose). So, I found another way to sort varcharas numbers:
我不知何故没能用CAST. 我总是得到Error Code: 1064 near "DECIMAL"（或我选择的其他数字类型）。所以，我找到了另一种varchar按数字排序的方法：
SELECT *
FROM mytable
ORDER BY ABS(mycol)
A bit simpler and works in my case.
稍微简单一点，在我的情况下有效。
回答by Quassnoi
SELECT *
FROM mytable
ORDER BY
CAST(mycol AS DECIMAL)
回答by aizaz
Here is the solution
这是解决方案
SELECT * FROM MyTable ORDER BY ABS(MyCol);
回答by Guido
All other answers use ABS, which converts the values into absolute (positive) values, assuming that the integers are positive. A better solution would be to use * 1:
所有其他答案都使用ABS，假设整数为正，它将值转换为绝对（正）值。更好的解决方案是使用* 1：
SELECT *
FROM mytable
ORDER BY mycol * 1
SELECT *
FROM mytable
ORDER BY mycol * 1
This to prevent casting negative numbers into positive ones. Inspired by: mysql sort string number
这是为了防止将负数转换为正数。灵感来源：mysql 排序字符串编号
回答by Guido
You can ABS()for this purpose. ABS() is a mathematical function that returns the absolute (positive) value of the specified expression. So query will be something like this
您可以ABS()为此目的。ABS() 是一个数学函数，它返回absolute (positive) value of the specified expression. 所以查询将是这样的
SELECT * FROM MyTable ORDER BY ABS(MyCol);
回答by Rabesh Lal Shrestha
You Can Order varchar field using this code according to your required
您可以根据需要使用此代码订购 varchar 字段
SELECT * FROM mytable ORDER BY ABS(mycol)
