使用 CAST 运算符将 VARCHAR 列排序为 FLOAT 在 MySQL 中不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10633695/
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
Sorting a VARCHAR column as FLOAT using the CAST operator don't work in MySQL
提问by Nicolas BADIA
I can't find a way to sort a varchar column casted as float. Here is my SQL request:
我找不到对转换为浮点数的 varchar 列进行排序的方法。这是我的 SQL 请求：
SELECT guid, number FROM table ORDER BY 'CAST(number AS FLOAT) DESC'
The "number" column is defined like this:
“数字”列的定义如下：
number varchar(20) ascii_general_ci
And the values defined in this column for my test are :
在此列中为我的测试定义的值是：
0.00
200.00
20.00
100.00
MySQL totally ignore the CAST operator and sort the columns by guid...
MySQL 完全忽略 CAST 运算符并按 guid 对列进行排序...
Is there a bug in MySQL or did I do something wrong ?
MySQL 中是否存在错误或我做错了什么？
回答by Devart
Try this trick (helps to sort strings as numbers)-
试试这个技巧（有助于将字符串排序为数字）-
SELECT guid, number FROM table ORDER BY number * 1 DESC
It will help MySQL to cast string to number.
它将帮助 MySQL 将字符串转换为数字。
Another solution -
另一种解决方案 -
...CAST(value as DECIMAL(10,5))
回答by NARENDRA
If you have taken GUID then the size should be varchar(40)Insted of that you can use uuid()
如果您使用了 GUID，那么大小应该是varchar(40)您可以使用的大小uuid()
I have done it using
我已经使用
select uuid(), number order by 'cast(number as float) desc';
It's working fine. If this is not you want can you send your entire code?
它工作正常。如果这不是您想要的，您可以发送您的整个代码吗？
