mysql order by某些条件和不区分大小写

Question

我想要排序

“特殊字符-数字-字母-其他-空格”

​

并使用以下查询

SELECT title FROM table ORDER BY 
    (case when ASCII(SUBSTRING(title, 1)) = 0 then 9
        when (ASCII(SUBSTRING(title, 1)) >= 33 and ASCII(SUBSTRING(title, 1)) <= 47) then 1
        when (ASCII(SUBSTRING(title, 1)) >= 58 and ASCII(SUBSTRING(title, 1)) <= 64) then 2
        when (ASCII(SUBSTRING(title, 1)) >= 91 and ASCII(SUBSTRING(title, 1)) <= 96) then 3
        when (ASCII(SUBSTRING(title, 1)) >= 123 and ASCII(SUBSTRING(title, 1)) <= 126) then 4
        when (ASCII(SUBSTRING(title, 1)) >= 48 and ASCII(SUBSTRING(title, 1)) <= 57) then 5
        when ASCII(SUBSTRING(title, 1)) > 128 then 7
        when ASCII(SUBSTRING(title,1)) = 32 then 8
        else 6 end ),binary(title)";

通过这个查询，我得到了想要的结果。

​

但有一件事，

​

字母顺序结果为(A，B，C，a，b，c)

我希望结果为(A，a，B，b，C，c)

​

我怎么才能做到这一点？

Answer 1

做你想做的事情是非常困难的。我建议放松一些限制，只需遵循ascii顺序，除了将空格更改为一些高字符：

ORDER BY REPLACE(title,' ',CONVERT(_utf32 0x10FFFF USING utf8mb4)),BINARY(title);