在 MySQL 中以数字方式对 varchar 字段进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4686849/
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 varchar field numerically in MySQL
提问by wow
I have a field numberof type varchar. Even though it is of type varchar, it stores integer values with optional leading zeros. A sort orders them lexicographically ("42"comes before "9"). How can I order by numeric values ("9"to come before "42")?
我有一个number类型的字段varchar。即使它是 type varchar，它也存储带有可选前导零的整数值。排序按字典顺序排列它们（"42"在之前"9"）。如何按数值排序（"9"在之前"42"）？
Currently I use the query:
目前我使用查询：
SELECT * FROM table ORDER BY number ASC
回答by bobs
Try this
尝试这个
SELECT * FROM table_name ORDER BY CAST(field_name as SIGNED INTEGER) ASC
回答by paxdiablo
There are a few ways to do this:
有几种方法可以做到这一点：
- Store them as numeric values rather than strings. You've already discounted that as you want to keep strings like
00100intact with the leading zeros. - Order by the strings cast as numeric. This will work but be aware that it's a performance killer for decent sized databases. Per-row functions don't really scale well.
- Add a third column which is the numeric equivalent of the string and index on that. Then use an
insert/updatetrigger to ensure it's set correctly whenever the string column changes.
- 将它们存储为数值而不是字符串。您已经打折了，因为您希望
00100使用前导零保持字符串完整。 - 按字符串排序为数字。这会奏效，但请注意，它是大型数据库的性能杀手。每行函数不能很好地扩展。
- 添加第三列，它是字符串和索引的数字等价物。然后使用
insert/update触发器确保在字符串列更改时正确设置它。
Since the vast majority of databases are read far more often than written, this third option above amortises the cost of the calculation (done at insert/update) over all selects. Your selects will be blindingly fast since they use the numeric column to order (and no per-row functions).
由于绝大多数数据库的读取次数远多于写入次数，因此上面的第三个选项可以分摊所有选择的计算成本（在insert/ 处完成update）。您的选择将非常快，因为它们使用数字列进行排序（并且没有每行函数）。
Your inserts and updates will be slower but that's the price you pay and, to be honest, it's well worth paying.
您的插入和更新会更慢，但这是您付出的代价，老实说，这是非常值得付出的。
The use of the trigger maintains the ACID properties of the table since the two columns are kept in step. And it's a well-known idiom that you can usually trade off space for time in most performance optimisations.
由于两列保持同步，触发器的使用维护了表的 ACID 属性。这是一个众所周知的习惯用法，您通常可以在大多数性能优化中权衡时间。
We've used this "trick" in many situations, such as storing lower-cased versions of surnames alongside the originals (instead of using something like tolower), lengths of identifying strings to find all users with 7-character ones (instead of using len) and so on.
我们在许多情况下都使用了这个“技巧”，例如将小写版本的姓氏与原始姓氏一起存储（而不是使用类似tolower），识别字符串的长度以查找具有 7 个字符的所有用户（而不是使用len）等等。
Keep in mind that it's okay to revert from third normal form for performance provided you understand (and mitigate) the consequences.
请记住，只要您了解（并减轻）后果，就可以从性能的第三范式恢复。
回答by workdreamer
Actually i've found something interesting:
其实我发现了一些有趣的事情：
SELECT * FROM mytable ORDER BY LPAD(LOWER(mycol), 10,0) DESC
This allows you to order the field like:
这允许您对字段进行排序，例如：
1
2
3
10
A
A1
B2
10A
111
回答by shantanuo
SELECT * FROM table ORDER BY number + 0
回答by alexii
Trick I just learned. Add '+0' to the varchar field order clause:
我刚学会的技巧。将 '+0' 添加到 varchar 字段顺序子句中：
SELECT * FROM table ORDER BY number+0 ASC
I now see this answer above. I am wondering if this is typecasting the field and an integer. I have not compared performance. Working great.
我现在看到上面的这个答案。我想知道这是否正在对字段和整数进行类型转换。我没有比较性能。工作得很好。
回答by ChAnDu353
For a table with values like Er353, ER 280, ER 30, ER36 default sort will give ER280 ER30 ER353 ER36
对于具有 Er353、ER 280、ER 30、ER36 等值的表，默认排序将给出 ER280 ER30 ER353 ER36
SELECT fieldname, SUBSTRING(fieldname, 1, 2) AS bcd,
CONVERT(SUBSTRING(fieldname, 3, 9), UNSIGNED INTEGER) AS num
FROM table_name
ORDER BY bcd, num;
the results will be in this order ER30 ER36 ER280 ER353
结果将按此顺序 ER30 ER36 ER280 ER353
回答by Rabesh Lal Shrestha
you can get order by according to your requirement my using following sql query
您可以根据您的要求使用以下 sql 查询获得订单
SELECT * FROM mytable ORDER BY ABS(mycol)
回答by minusf
given a column usernamecontaining VARCHAR's like these:
给定一个username包含VARCHAR's的列，如下所示：
username1
username10
username100
one could do:
可以这样做：
SELECT username,
CONVERT(REPLACE(username, 'username', ''), UNSIGNED INTEGER) AS N
FROM users u
WHERE username LIKE 'username%'
ORDER BY N;
it is not cheap, but does the job.
它并不便宜，但可以胜任。
回答by Or Weinberger
SELECT * FROM table ORDER BY number ASC
Should display what you want it to display.. looks like you're sorting it by idor numberis not defined as integerat the moment.
应显示你想让它显示..看起来像你通过排序它什么id或number没有被定义为integer的时刻。
回答by user3816758
Rough and ready: order by 1*field_name
粗略准备：按 1*field_name 排序
