mysql命令字符串作为数字 [英] mysql order string as number

问题描述

我已经搜索了几天，寻找一种在mysql中将字符串排序为数字的方法.

I have searched for days for a way to sort a string as number in mysql.

我的行是这样的:

order_column

order_column

1.1.2
1.1.100
1.1.1

按此列在mysql中的升序将产生:

Ordering ascending by this column in mysql will produce:

1.1.1
1.1.100
1.1.2

我有兴趣获得以下结果:

I am interested in getting the following result:

1.1.1
1.1.2
1.1.100

是否可以使用SQL产生此结果?

Is there a way to produce this result using SQL?

我的列的其他可能值:

28.1999.1.1
1
1.1.154.20
100.1.1.1.1.15

谢谢，我感谢大家的时间.

Thanks, i appreciate everybody's time.

存储树的快速关系方法数据(例如文章的主题评论) 看看第一个答案，艾曼·霍里耶(Ayman Hourieh)的答案.我正在尝试使该解决方案起作用.我的网站每篇文章的评论不超过1万条，因此我找到了以下解决方法:

Fast Relational method of storing tree data (for instance threaded comments on articles) Take a look at the first answer, the one by Ayman Hourieh. I am trying to get that solution working. My website has no more than 10k comments per article, so i found a work-around like this:

000001
000002
000002.000001
000002.000001.000001
000002.000002
000002.000003
000003

基本上，将零放在我的注释计数器之前，以使字符串比较不会失败.但这仅适用于99999条评论.我可以添加更多的零，可以说10个零，这将适用于999999999条评论，但我希望有一个更优雅的解决方案.

Basically, putting zeros before my comment counter so that string comparison doesn't fail. But this will only work for 99999 comments. I can add more zeros, lets say 10 zeros, and that will work for 999999999 comments, but i was hoping for a more elegant solution.

推荐答案

如果字符串不太长，则可以

If string is not too long you can do

ORDER BY cast(replace(your_column_name,'.','') as unsigned);

这篇关于mysql命令字符串作为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！