使用混合数据类型的mysql自定义排序

Question

在下面的MySQL查询中，我正在使用自定义的order by语句，以便按特定顺序显示各种大小，而不是按字母顺序排序：

select distinct size 
from product p left join productsizes ps 
             on p.productcode = ps.size_prodcode 
order by field(size, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')

在某些产品还有数字尺码的情况下，如何编写排序规则以使其按照自定义顺序将数字尺码升序排列？

期望输出示例：

30, 32, 34, S, M, L

或者

S, M, L, 30, 32, 34

- bikey77

---

做一个非常丑陋的 case 可能会起作用... - Ricardo Marimon

Answer 1

FIELD()函数在未找到搜索字符串时返回0。因此：

ORDER BY FIELD(size, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'), size

- eggyal

---

非常棒的答案。使用FIELD()函数将字符串和数字分离是一种创新的用法。 - The Thirsty Ape

Answer 2

尝试

ORDER BY (CASE WHEN sizes REGEXP '(\d)+' THEN 0 ELSE 1 END) ASC,
         field(sizes, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')

- John Woo

Answer 3

你应该尝试使用一个枚举字段来存储所有的尺寸，它可以维护排序顺序。

CREATE TABLE ... (
  ...
  size ENUM ('30', '32', '34', 'S', 'M', 'L')
  ...
);

或者

CREATE TABLE ... (
  ...
  size ENUM ('S', 'M', 'L', '30', '32', '34')
  ...
);

- eisberg

---

但是当数据添加到这个表中时会发生什么？如何更新排序顺序？ - bikey77