MySQL实现字段的自定义排序的方法_Mysql

数据

首先有一张产品表

create table `product` (
  `product_id` varchar(50) collate utf8mb4_bin not null,
  `brand_id` varchar(50) collate utf8mb4_bin default null,
  `brand` varchar(200) collate utf8mb4_bin default null,
  `product_type` varchar(3) collate utf8mb4_bin default null comment 's-single item, k-product kit',
  `name_en` varchar(200) collate utf8mb4_bin not null,
  `name_t_ch` varchar(240) collate utf8mb4_bin default null,
  `name_s_ch` varchar(240) collate utf8mb4_bin default null,
    primary key (`product_id`)
    ) engine=innodb auto_increment=1702 default charset=utf8mb4 collate=utf8mb4_bin

一些数据：

具体的数据可以自己插入一些测试数据进行测试，我的数据里面已经是有了很多各种数据，包括了下面排序要求的数据了。

需求

最近遇到一个这样一个需求，针对页面上的展示效果需要一个特定的排序，即我想看到这些特定的产品id需要排在前面，然后后面按照我需要的顺序进行排序。

之前的排序顺序是按照product id的升序排序的，字符串的排序。

select product_id, name_t_ch from product p order by product_id ;

下面则是要求修改后的排序顺序：

解决方案

使用代码实现排序（不是很好）

因为这个是在页面上有显示，并且是有分页，如果使用代码排序的话，则需要查出所有数据然后再手动分页，用不上数据库的分页了。

使用mysql的排序来实现（推荐）

使用 order by field(column, str1,str2,str3)

格式：field(value,str1,str2,str3,str4)，value与str1、str2、str3、str4比较，返回1、2、3、 4，如遇到null或者不在列表中的数据则返回0

简单版本的一个排序：

select product_id , name_t_ch from product p order by field(product_id, 'mr-0800004', 'mr-0800007', 'mr-0800001', 'mr-0800002') , product_id ;

结果

第一个sql版本：

-- 初始版本
select product_id, name_t_ch from product p
order by field(
  case 
    when product_id like 'mr-%' then 'mr'
    when product_id like 'sd-%' then 'sd'
    when product_id like 'wn-%' then 'wn'
    when product_id like 'hhc-%' then 'hhc'
    when product_id like 'hs-%' then 'hs'
    when product_id like 'g-%' then 'g'
    else 'other' 
  end,
  'mr', 'sd', 'wn', 'hhc', 'hs', 'g', 'other', product_id
);

排序结果：

第二个sql 版本：

-- 改造版本
select product_id, name_t_ch from product p
order by field(
  case 
    when product_id = 'mp-0800004' then 'mp-0800004'
    when product_id = 'mp-0800007' then 'mp-0800007'
    when product_id = 'mr-0800001' then 'mr-0800001'
    when product_id = 'mr-0800002' then 'mr-0800002'
    when product_id = 'mr-0800004' then 'mr-0800004'
    when product_id = 'sd-0800001' then 'sd-0800001'
    when product_id like 'wn-%' then 'wn'
    when product_id like 'hhc-%' then 'hhc'
    when product_id like 'hs-%' then 'hs'
    when product_id like 'g-%' then 'g'
    else 'other'
  end,
  'mp-0800004', 'mp-0800007', 'mr-0800001', 'mr-0800002', 'mr-0800004',
  'sd-0800001', 'wn', 'hhc', 'hs', 'g', 'other', product_id
);

结果：

能够看到排在前面的几个 product_id 是按照我们需要的顺序排序了的。

gpt解释：在这里在 field 函数中，第一个参数指定了要排序的字段或表达式，而后续的参数指定了排序的顺序。在您提供的查询中，case when 表达式返回的结果是字符串，这些字符串用于指定排序顺序。因此，product_id 不能直接写在 field 函数的第一个参数中。

使用case when 转换实现自定义排序

select product_id , name_t_ch from product p
order by
  case 
    when product_id like 'mr-%' then 1
    when product_id like 'sd-%' then 2
    when product_id like 'wn-%' then 3
    when product_id like 'hhc-%' then 4
    when product_id like 'hs-%' then 5
    when product_id like 'g-%' then 6
    else 7 -- 其他情况
  end,
product_id;

排序结果，按照了我们指定的顺序进行排序

这个查询首先根据 productid 是否以特定前缀开头进行分类，并为每个分类分配一个排序权重。然后，按照这个排序权重进行排序。如果 productid 不以任何指定的前缀开头，则默认按照原始的 productid 进行排序。

-- 如果前面还有指定的顺序的话
select product_id, name_t_ch from product p
order by 
  case 
    when product_id = 'mp-0800004' then 1
    when product_id = 'mp-0800007' then 2
    when product_id = 'mr-0800001' then 3
    when product_id = 'mr-0800002' then 4
    when product_id = 'mr-0800004' then 5
    when product_id = 'sd-0800001' then 6
    when product_id like 'wn-%' then 7
    when product_id like 'hhc-%' then 8
    when product_id like 'hs-%' then 9
    when product_id like 'g-%' then 10
    else 11
  end,
product_id;

结果

从结果中可以看到已经按照我们需要的顺序排好了序。case when 的写法更简洁，使用这个！

小结

最后这个也可以加上分页的效果。

select product_id, name_t_ch from product p
order by 
  case 
    when product_id = 'mp-0800004' then 1
    when product_id = 'mp-0800007' then 2
    when product_id = 'mr-0800001' then 3
    when product_id = 'mr-0800002' then 4
    when product_id = 'mr-0800004' then 5
    when product_id = 'sd-0800001' then 6
    when product_id like 'wn-%' then 7
    when product_id like 'hhc-%' then 8
    when product_id like 'hs-%' then 9
    when product_id like 'g-%' then 10
    else 11
  end,
product_id limit 0, 5;

第二页

select product_id, name_t_ch from product p
order by 
  case 
    when product_id = 'mp-0800004' then 1
    when product_id = 'mp-0800007' then 2
    when product_id = 'mr-0800001' then 3
    when product_id = 'mr-0800002' then 4
    when product_id = 'mr-0800004' then 5
    when product_id = 'sd-0800001' then 6
    when product_id like 'wn-%' then 7
    when product_id like 'hhc-%' then 8
    when product_id like 'hs-%' then 9
    when product_id like 'g-%' then 10
    else 11
  end,
product_id limit 1, 5;