MySQL 如何按 varchar 字段作为数字排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11728683/
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
How to ORDER BY varchar field as number?
提问by waza123
I have problem with sort/order by, not working like I need.
我在排序/排序方面遇到问题，无法按我需要的方式工作。
SELECT `proc` FROM `table` ORDER BY `proc` DESC;
Result:
结果：
80.0 proc
70.0 proc
60.0 proc
50.0 proc
40.0 proc
200.0 proc
20.0 proc
190.0 proc
180.0 proc
170.0 proc
160.0 proc
150.0 proc
140.0 proc
130.0 proc
120.0 proc
110.0 proc
100.0 proc
What I need is:
我需要的是：
200.0 proc
190.0 proc
180.0 proc
170.0 proc
160.0 proc
150.0 proc
140.0 proc
130.0 proc
120.0 proc
110.0 proc
100.0 proc
90.0 proc
80.0 proc
70.0 proc
60.0 proc
50.0 proc
40.0 proc
20.0 proc
How to do it ?
怎么做 ？
回答by che
It looks like "proc" is a string (varcharfield), so it gets ordered lexically. If it is so, you can probably order it by
看起来“proc”是一个字符串（varchar字段），因此按词法排序。如果是这样，您可能可以通过以下方式订购
SELECT `proc` FROM `table` ORDER BY convert(`proc`, decimal) DESC;
Please note that such queries will be very slow, and for any serious usage it's better to use numeric columns for storing numeric data.
请注意，这样的查询会非常慢，对于任何严重的使用，最好使用数字列来存储数字数据。
回答by Ray
The column field for proc is a VARCHAR or CHAR and it's treating it as a literal string--sorting alphabetically.
proc 的列字段是 VARCHAR 或 CHAR，它将其视为文字字符串 - 按字母顺序排序。
Convert the column to double or float or cast the value
将列转换为 double 或 float 或强制转换值
SELECT `proc` FROM `table` ORDER BY CAST(`proc` AS decimal) DESC;
回答by Bruno
There's something fundamentally wrong with your table design. Instead of using values like '80.0 proc'in a VARCHARcolumn, you should just keep 80.0in a column of type REAL(or any suitable numerical type that's appropriate for your data). You could do dynamic conversion, only to be used in the ORDER BYexpression, but this is also likely to deteriorate the performance of your query.
你的桌子设计有一些根本性的错误。不要像'80.0 proc'在VARCHAR列中那样使用值，您应该只保留80.0在类型REAL（或适合您的数据的任何合适的数字类型）的列中。您可以进行动态转换，仅用于ORDER BY表达式中，但这也可能会降低查询的性能。
Adding "proc" to your text here doesn't seem useful, and it will also prevent you from doing a simple conversion.
在此处将“ proc”添加到您的文本中似乎没有用，而且还会阻止您进行简单的转换。
Surprisingly (see che's answer), apparently, convert(..., decimal)is capable of ignoring the trailing rubbish. It's not something you should rely on in general, though.
令人惊讶的是（参见che的回答），显然，convert(..., decimal)能够忽略尾随的垃圾。不过，这通常不是您应该依赖的东西。
The documentation on this aspect of the conversionisn't particularly clear. It's worth reading that section to be aware of the limitations of string/numbers (which would happen in general), for example:
关于转换这方面的文档不是特别清楚。值得阅读该部分以了解字符串/数字的限制（通常会发生），例如：
mysql> SELECT '18015376320243459' = 18015376320243459; -> 0
mysql> SELECT '18015376320243459' = 18015376320243459; -> 0
If that behaviour changed, you could probably use replace()in this case, just to get rid of ' proc'.
如果该行为发生变化，您可能可以replace()在这种情况下使用，只是为了摆脱' proc'.
For something more complex, you could potentially use a regular expression replacement to extract the numerical value from your string and cast it into a number before sorting, but this is not supported out of the box in MySQLand that would be rather clunky anyway (fix the problem at its source: your column data type).
对于更复杂的事情，您可能会使用正则表达式替换从字符串中提取数值并在排序之前将其转换为数字，但在 MySQL 中不支持开箱即用，无论如何这会相当笨重（修复问题的根源：您的列数据类型）。
To deal with your legacy data, you could add an extra column and use an external program (in any language that's capable of doing a regexp replace), which shouldn't be too difficult.
为了处理您的遗留数据，您可以添加一个额外的列并使用外部程序（使用任何能够进行正则表达式替换的语言），这应该不会太困难。
回答by antonienko
If you always have the same number of decimal points, my approach would be:
如果你总是有相同数量的小数点，我的方法是：
SELECT `proc` FROM `table` ORDER BY LENGTH(proc) DESC, proc DESC;
