像这样试试。我刚用了w3schools的数据库，需要检查一下mysql是否工作。

SELECT * FROM myTable GROUP BY name ORDER BY UPPER(name), LOWER(name);

这将创建一个新列'newid'，并按您需要的顺序排列

select newid = row_number() over (order by case when val between 'A' and 'Z' 
                                          then  ascii(val) -31 
                                          else ascii(val) end), *

from yourtable

编辑
这将是最简单的方法，将dds的思想转变为适用于所有mysql版本的更通用的工作方式。。
查询

SELECT 
   id
 , name
FROM 
 Table1
ORDER BY 
  CASE
    WHEN name COLLATE latin1_bin BETWEEN 'A' AND 'Z'
    THEN ASCII(name) + 31
    ELSE ASCII(name)
   END

结果

| id  | name |
| --- | ---- |
| 4   | A    |
| 1   | a    |
| 5   | B    |
| 2   | b    |
| 6   | C    |
| 3   | c    |

db fiddle视图
解释视图以便您可以看到db fiddle上发生了什么
或者更稳定的一个，因为为计算的\u ascii \u值列生成的值是唯一的。解释视图以便您可以看到db fiddle上发生了什么
另一个查询相当复杂。。
查询

SELECT 
    Table1.id
  , Table1.name 
FROM (

SELECT 
 DISTINCT
   (
     SUBSTRING_INDEX(SUBSTRING_INDEX(@orderList, ',', number_generator.number), ',', -1)
     COLLATE utf8mb4_bin
   ) AS letter
FROM (

SELECT
 (@number := @number + 1) AS number
FROM (
 SELECT 1 AS number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS row_1
CROSS JOIN (
 SELECT 1 AS number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
) AS row_2
CROSS JOIN (SELECT @number := 0) AS init_user_param 

) AS number_generator
CROSS JOIN (SELECT @orderList := 'A,a,B,b,C,c' /* add all to Z,z */) AS init_user_param

) AS letters
INNER JOIN
 Table1
ON
 letters.letter = Table1.name
;

结果

| id  | name |
| --- | ---- |
| 4   | A    |
| 1   | a    |
| 5   | B    |
| 2   | b    |
| 6   | C    |
| 3   | c    |

db fiddle视图
注意有些事情你应该知道 COLLATE utf8mb4_bin 可能更改为 COLLATE utf8_bin 相反，当您的mysql使用utf8字符集时。
以及 SELECT @orderList := 'A,a,B,b,C,c' /* add all to Z,z */ 你需要添加更多 Z,z