乐趣区

关于mysql:MySQL如何做如等级成色等特殊顺序的排序

jiezi

前言：

在波及商品类的我的项目时，为了给同一类商品定位，往往会分等级或者成色。而等级 / 成色有时是用相似 A，A+，A+1，K，L1，L2 等英文与数字的组合进行表述。既然是等级，天然就会辨别高等级，低等级。有高有低，又给商品定了等级，所以到了商品列表展现，等级排序天然少不了。

我的项目在开始做的时候对于商品等级字段是间接存储的下面的字母数字，也就是 A，A+ 1 之类的。所以在须要以等级排序的时候，以英文字母的形式升序倒序就呈现了问题，比方大部分等级中，S 是最高级，而英文倒序 S 排在了前面。

抵触：

间接以英文字母排序很显然是不行的，于是首先想到的是数据表的等级字段改为 int，存入数字。在配置里或者独自建一张表与对应数字关联一个等级。相似 K 对应 1 之类的，须要升序降序就对数字排序，这样就解决了 S 排序问题。然而，事实的是商品表的数据量极大，如果要给原来的等级做关联，就要小心的写 SQL 替换或者手动一个一个改。不晓得有没有在不须要批改表的状况下解决那种特殊字符排序的办法，起初还真想到了一个办法。

解决办法：

通过应用 MySQL 的 find_in_set 函数，通过自定义的程序进行排序，所以后续将某个等级提前改变都不会那么被动了，如下示例。

1. 能够先定义升序，倒序的排列 

"grade_desc" => ["S","A+","A1","A2","B+1","B+2","B1","B2","C+1","C+2","C1","C2","D+1","D+2","D1","D2","D3","E+1","E+2","E1","E2","F","G+","G1","G2","H+","H1","H2","I+","I1","I2","J+","J","K+","K"]"grade_asc" => ["K","K+","J","J+","I2","I1","I+","H2","H1","H+","G2","G1","G+","F","E2","E1","E+2","E+1","D3","D2","D1","D+2","D+1","C2","C1","C+2","C+1","B2","B1","B+2","B+1","A2","A1","A+","S"]

2. 查问示例

3. ThinkPHP5 的应用形式

3.1. 升序 

$list = GoodsItem::where('delete_time','=',0)
            ->field('id,grade')
            ->orderRaw("find_in_set(grade,'K,K+,J,J+,I2,I1,I+,H2,H1,H+,G2,G1,G+,F,E2,E1,E+2,E+1,D3,D2,D1,D+2,D+1,C2,C1,C+2,C+1,B2,B1,B+2,B+1,A2,A1,A+,S')")
            ->select();

return $list->toArray();

3.2. 倒序 

$list = GoodsItem::where('delete_time','=',0)
            ->field('id,grade')
            ->orderRaw("find_in_set(grade,'S,A+,A1,A2,B+1,B+2,B1,B2,C+1,C+2,C1,C2,D+1,D+2,D1,D2,D3,E+1,E+2,E1,E2,F,G+,G1,G2,H+,H1,H2,I+,I1,I2,J+,J,K+,K')")
            ->select();
        return $list->toArray();

3.3. 成果

4. find_in_set 函数

这个函数能够放在 where 中应用，查问字段是否存在某个范畴内，有点像 IN，但还是有区别，具体的前期补上。

退出移动版