MySQL mysql排序字符串编号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5417381/
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 sort string number
提问by sreenavc
I have a column of type varchar that stores many different numbers. Say for example there are 3 rows: 17.95, 199.95 and 139.95.How can i sort that field as numbers in mysql
我有一个 varchar 类型的列，它存储许多不同的数字。比如说有 3 行：17.95、199.95 和 139.95。如何在 mysql 中将该字段作为数字进行排序
采纳答案by Devart
Use a CAST or a CONVERT function.
使用 CAST 或 CONVERT 函数。
回答by RichardTheKiwi
Quickest, simplest? use * 1
最快、最简单？用* 1
select *
from tbl
order by number_as_char * 1
The other reasons for using * 1are that it can
使用的其他原因* 1是它可以
- survive some horrendous mishaps with underflow (reduced decimal precision when choosing what to cast to)
- works (and ignores) columns of purely non-numeric data
- strips numeric portions of alphanumeric data, such as 123A, 124A, 125A
- 幸免于下溢的一些可怕事故（在选择要转换的内容时降低小数精度）
- 工作（并忽略）纯非数字数据列
- 去除字母数字数据的数字部分，例如 123A、124A、125A
回答by MarzSocks
If you need to sort a char column containing text AND numbersthen you can do this.
如果您需要对包含文本和数字的字符列进行排序，那么您可以这样做。
tbl contains: 2,10,a,c,d,b,4,3
tbl 包含：2,10,a,c,d,b,4,3
select * from tbl order by number_as_char * 1 asc, number_as_char asc
expected output: 2,3,4,10,a,b,c,d
预期产出：2,3,4,10,a,b,c,d
If you don't add the second order by argument only numbers will be sorted - text actually gets ignored.
如果您不按参数添加第二个顺序，则只会对数字进行排序 - 文本实际上会被忽略。
回答by Marius95
Pad the string with leading zeroes:
用前导零填充字符串：
ORDER BY LPAD(`column`,<max length of string>,"0")
回答by WhiteFang34
If you really have to you can do this if your source data is compatible:
如果您真的需要，如果您的源数据兼容，您可以这样做：
SELECT column FROM table ORDER BY CAST(column AS DECIMAL(10,2))
It's not going to be very fast for large data sets though. If you can you should change the schema to use DECIMALin the first place though. Then it can be properly indexed for better performance.
不过，对于大型数据集，它不会很快。如果可以，您应该首先更改要使用的架构DECIMAL。然后它可以被正确地索引以获得更好的性能。
回答by Zon
This approach is helpful when sorting text as numbers:
这种方法在将文本排序为数字时很有用：
SELECT `my_field`
FROM `my_table`
ORDER BY `my_field` + 0;
Found the solution on http://crodrigues.com/trick-mysql-order-string-as-number/.
在http://crodrigues.com/trick-mysql-order-string-as-number/上找到了解决方案。
