MySQL 如何在mySQL中订购1,2,3而不是1,10,11,12
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1833077/
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 1,2,3 not 1, 10, 11, 12 in mySQL
提问by shin
The following code outputs in order of 1, 10, 11, 12 of id.
下面的代码按照id的1、10、11、12的顺序输出。
I want to make it 1,2,3,4...
我想把它变成 1,2,3,4 ...
Could anyone tell me what I should do please.
谁能告诉我我应该怎么做。
$Q = $this->db->query('SELECT P.*, C.Name AS CatName FROM products AS P LEFT JOIN categories C ON C.id = P.category_id');
Thanks in advance.
提前致谢。
回答by Scott Saunders
First, add an order by clause at the end:
首先，在最后添加一个 order by 子句：
ORDER BY category_id
If category_id is a string, then you must treat it like an integer. There are a few ways to do this. I usually add a zero. You can also cast it.
如果 category_id 是字符串，则必须将其视为整数。有几种方法可以做到这一点。我通常加一个零。你也可以投射它。
ORDER BY category_id + 0
回答by Andreas Bergstr?m
As previously mentioned MySQL doesn't support alphanumeric sorting. One common trick to solve this is to first order by length:
如前所述，MySQL 不支持字母数字排序。解决这个问题的一个常见技巧是按长度排序：
ORDER BY LENGTH(column_name), column_name
As long as the non-numeric part of the value is the same length, this will sort 1 before 10, 10 before 100, etc.
只要值的非数字部分长度相同，这将在 10 之前排序 1、在 100 之前排序 10，等等。
回答by halfdan
You can do an explicit cast by doing:
您可以通过执行以下操作进行显式转换：
ORDER BY CAST(category_id AS UNSIGNED INTEGER)
But you should reconsider you database layout as a field containing only numeric values should also be of an numeric type..
但是您应该重新考虑您的数据库布局，因为仅包含数值的字段也应该是数字类型。
Best wishes, Fabian
最好的祝福，法比安
回答by Jakub
Make sure that the column that holds 1,2,3,4 is INTtype, if it is TEXT, you will not get numerical order, but what you describe 1, 10, 11, 2, 22, 23, 31, etc;
确保保存1,2,3,4的列是INTtype，如果是TEXT，你不会得到数字顺序，而是你描述的1,10,11,2,22,23,31等;
And like others mentioned, use ORDER BY
和其他人提到的一样，使用 ORDER BY
回答by CHanaka
Order byonly working for numerical values(int), not work for varchar, char
按仅适用于数值（int）排序，不适用于varchar、char
Your category_id should numerical, otherwise you need to cast values to numerical.
您的 category_id 应该是数字，否则您需要将值转换为数字。
回答by jensgram
Well, you're not setting any ORDER BYclause.
好吧，你没有设置任何ORDER BY条款。
