在MySQL中尝试自然排序失败

Question

我试图在MySQL中实现自然/字母数字排序，但没有运气。我读过几篇关于我在google搜索中找到的主题的Stack文章和各种文章。下面是我开始的基本查询：

select VideoTitle, VideoID
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY VideoTitle

该查询产生这个结果集：

被捕的发展第一季: Ep。1试点被捕发展第一季: Ep。10码头压力停止发展第一季: Ep。2“顶级香蕉被捕”第一季: Ep。3提起Buster被捕的发展第一季: Ep。4关键决定阻碍了发展第一季: Ep。5慈善道被捕发展第一季: Ep。6访问我们的被捕发展第一季: Ep。7“上帝啊，我们相信被逮捕的发展”第一季: Ep。我妈妈，这辆车被扣押了第一季: Ep。攻占城堡

请注意，从自然排序的角度来看，这一行是无序的：

被捕的发展第一季: Ep。10墩压力

根据我读到的帖子/文章，我也尝试了这些方法，并附上了它们的结果集：

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY CAST(VideoTitle AS UNSIGNED), VideoTitle

被捕的发展第一季: Ep。1试点被捕发展第一季: Ep。10码头压力停止发展第一季: Ep。2“顶级香蕉被捕”第一季: Ep。3提起Buster被捕的发展第一季: Ep。4关键决定阻碍了发展第一季: Ep。5慈善道被捕发展第一季: Ep。6访问我们的被捕发展第一季: Ep。7“上帝啊，我们相信被逮捕的发展”第一季: Ep。我妈妈，这辆车被扣押了第一季: Ep。攻占城堡

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY LENGTH(VideoTitle), VideoTitle

被捕的发展第一季: Ep。1试点被捕发展第一季: Ep。2“顶级香蕉被捕”第一季: Ep。4关键决定阻碍了发展第一季: Ep。5慈善道被捕发展第一季: Ep。6访问我们的被捕发展第一季: Ep。10码头压力停止发展第一季: Ep。7“上帝啊，我们相信被逮捕的发展”第一季: Ep。3提起Buster被捕的发展第一季: Ep。我妈妈，这辆车被扣押了第一季: Ep。攻占城堡

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY VideoTitle + 0 ASC

被捕的发展第一季: Ep。1试点被捕发展第一季: Ep。攻占城堡被捕第一季: Ep。我妈妈，这辆车被扣押了第一季: Ep。7“上帝啊，我们相信被逮捕的发展”第一季: Ep。6访问我们的被捕发展第一季: Ep。5慈善道被捕发展第一季: Ep。4关键决定阻碍了发展第一季: Ep。3提起Buster被捕的发展第一季: Ep。2“顶级香蕉被捕”第一季: Ep。10墩压力

有什么想法吗？

Answer 1

最好的解决方案是将节目标题、季、集号和集标题分别存储在不同的专栏中。

在这种情况下，您可能可以执行一些字符串操作来获得所需的东西。

ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(VideoTitle, ': ', -1), ' ', 2), ' ', -1)+0 

不过，这是一个非常脆弱的解决方案。

Answer 2

如果你真的想要的话，你可以把它按块分类：

order by substring_index(VideoTitle, ' Season ', 1),
         substring_index(VideoTitle, ' Season ', -1) + 0,
         substring_index(VideoTitle, ': Ep. ', -1) + 0

这假设字符串' Season ' ( : Ep. )在每个标题中只发生一次。

假设季节只有一位数，下面的版本也应该非常接近：

order by substring_index(VideoTitle, ': Ep. ', 1),
         substring_index(VideoTitle, ': Ep. ', -1) + 0

Answer 3

如果创建新列以提高模型的可搜索性，我将提供一个更优雅的解决方案，而不是将字符串分解为各个部分。

select VideoTitle 
  from (select VideoTitle, 
               VideoTitle REGEXP '.+Ep\\. [0-9]{2}.+' vd2dig 
          from videos
       ) sub
 order by vd2dig, VideoTitle; 

从本质上说，这将使VideoTitle在1中有两位数，而当它有一位数时，它将是0，所以我首先按此列排序，然后是VideoTitle上的自然顺序。

看到它在这里工作：http://sqlfiddle.com/#!9/6abde/1