mysql 自然排序

发布于 2024-11-06 15:39:52 字数 292 浏览 4 评论 0原文

我有像 server(id,name,ip) 这样的表。当我尝试按名称对结果进行排序时，我得到：

srv1,srv10,srv11,srv2,srv6

但我需要像 srv1,srv2,srv6,srv10,srv11 这样的结果代码>

我知道的一个想法是，

ORDER BY LENGTH(name), name

但我的名称列长度不同，

我需要做什么？

原文

I have table like server(id,name,ip). When I'm trying to sort results by name, I get:

srv1,srv10,srv11,srv2,srv6

but I need the results like srv1,srv2,srv6,srv10,srv11

One idea I know is

ORDER BY LENGTH(name), name

but I have different lengths in name column

What do I need to do?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

公布 2024-11-13 15:39:52

你可以试试这个：

SELECT id,name,ip,CONVERT(SUBSTRING(name FROM 4),UNSIGNED INTEGER) num
ORDER BY num;

You could try this:

SELECT id,name,ip,CONVERT(SUBSTRING(name FROM 4),UNSIGNED INTEGER) num
ORDER BY num;

回复收藏 0 原文

慈悲佛祖 2024-11-13 15:39:52

MySQL 中没有实现自然排序。您应该尝试不同的方法。在此示例中，我假设服务器名称始终具有相同的模板（即 srv###）。

select
    name, 
    mid(name, 4, LENGTH(name)-3) as num, 
    CAST(mid(name, 4, LENGTH(name)-3) AS unsigned) as parsed_num 
from server
order by parsed_num asc;

正如我所说，这种方法非常具体，因为您假设前 3 个字符将被忽略。如果您更改模板，这可能会产生误导并且难以处理。

您可以选择向表中添加一列，我们将其称为 prefix，您可以在其中设置服务器的前缀名称（在您的示例中，每个服务器的前缀名称将为 srv））。然后您可以使用：

select
    name,
    prefix,
    mid(name, LENGTH(prefix) + 1, LENGTH(name)-LENGTH(prefix)) as num, 
    CAST(mid(name, LENGTH(prefix) + 1, LENGTH(name)-LENGTH(prefix)) AS unsigned) as parsed_num  
from server
order by parsed_num asc;

获得更稳健的方法。

Natural sorting is not implemented in MySQL. You should try a different approach. In this example I assume that the server name has always the same template (i.e. srv###).

select
    name, 
    mid(name, 4, LENGTH(name)-3) as num, 
    CAST(mid(name, 4, LENGTH(name)-3) AS unsigned) as parsed_num 
from server
order by parsed_num asc;

As I said, this approach is very specific, since you assume that the first 3 characters are to be ignored. This could be misleading and difficult to handle if you change the template.

You could chose to add a column to the table, let's call it prefix in which you set the prefix name for the server (in your example it will be srv for each one). Then you could use:

select
    name,
    prefix,
    mid(name, LENGTH(prefix) + 1, LENGTH(name)-LENGTH(prefix)) as num, 
    CAST(mid(name, LENGTH(prefix) + 1, LENGTH(name)-LENGTH(prefix)) AS unsigned) as parsed_num  
from server
order by parsed_num asc;

obtaining a more robust approach.

回复收藏 0 原文

~没有更多了~