MySQL按字母数字排序不起作用

问题描述

在这种情况下，我使用这样的MySQL查询

Here's the case, I use a MySQL query like this

SELECT 
    *
FROM
    tbl_name
WHERE
    id = 1
ORDER BY LENGTH(title), title;

但是当有这样的标题

名侦探柯南第797.5集英语字幕

Detective Conan Episode 797.5 English Subbed

名侦探柯南829.2英语涂胶

Detective Conan Episode 829.2 English Subbed

它变为

名侦探柯南897集英语字幕

Detective Conan Episode 897 English Subbed

名侦探柯南898集英语字幕

Detective Conan Episode 898 English Subbed

名侦探柯南第797.5集英语字幕

Detective Conan Episode 797.5 English Subbed

名侦探柯南829.2英语涂胶

Detective Conan Episode 829.2 English Subbed

正确的人必须是这样

名侦探柯南第797.5集英语字幕

Detective Conan Episode 797.5 English Subbed

名侦探柯南829.2英语涂胶

Detective Conan Episode 829.2 English Subbed

名侦探柯南897集英语字幕

Detective Conan Episode 897 English Subbed

名侦探柯南898集英语字幕

Detective Conan Episode 898 English Subbed

在MySQL查询中是否可以按照我想要的进行排序?还是应该重新设计表结构，并将情节编号拆分到新列中.

Is it possible in a MySQL query to sort like what I want? or should I redesign the table structure and split the episode number in a new column.

谢谢.

推荐答案

我误解了您的最初要求.试试这个:

I misunderstood your initial request. Try this :

SELECT 
    *
FROM
    tbl_name
WHERE
ORDER BY CAST(title AS UNSIGNED), title;

在这里检查小提琴示例: http://sqlfiddle.com/#!9/755b97/2

Check fiddle example here : http://sqlfiddle.com/#!9/755b97/2

这似乎仅在数字位于最前面时起作用.

Edit : This seems to work only when numbers are at the front.

这似乎是解决您问题的唯一可行解决方案，但代价是负担沉重:

This seems to be the only working solution for your problem, but at the cost of heavy load : Natural Sort in MySQL

其他解决方案是您切换到mysql 8.0，它引入了regexp_replace，允许您从字符串中提取数字，然后从ORDER BY CAST(the_result_of_your_regexp)中提取.仍然会很重.

Other solution would be for you to switch to mysql 8.0, which introduces regexp_replace, allowing you to extract the numbers from your string, then ORDER BY CAST(the_result_of_your_regexp). Would still be heavy though.

这篇关于MySQL按字母数字排序不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！