MySQL自定义order by排序规则

数据表

create table tb_user
(
    id      bigint auto_increment
        primary key,
    name    varchar(16)  not null,
    age     int          not null,
    address varchar(128) null
);

INSERT INTO test.tb_user (id, name, age, address) VALUES (1, '张三', 18, 'China');
INSERT INTO test.tb_user (id, name, age, address) VALUES (2, '李四', 19, 'China');
INSERT INTO test.tb_user (id, name, age, address) VALUES (3, 'jack', 20, 'America');
INSERT INTO test.tb_user (id, name, age, address) VALUES (4, 'jane', 22, 'America');
INSERT INTO test.tb_user (id, name, age, address) VALUES (5, 'pig', 19, 'Japan');
INSERT INTO test.tb_user (id, name, age, address) VALUES (6, 'cat', 22, 'Japan');

一、使用field()函数自定义顺序

① 一个字段排序

优先China，其次America，最后Japan

select
    name, address
from
    tb_user
order by
    field(address, 'China', 'America', 'Japan') asc

asc可省略，如果有些数据不在指定范围内，则从最后开始按照默认排序

② 多个字段排序

先按照address升序排序，相同再按照name降序排序

select
    name, address
from
    tb_user
order by
    field(address, 'China', 'America', 'Japan') asc,
    field(name, '李四', '张三', 'jane', 'jack') desc

二、使用case when自定义排序规则

① 一个字段

优先China，其次America，最后Japan

select
    name, address
from
    tb_user
order by
    case address
        when 'China' then  1
        when 'America' then 2
        when 'Japan' then 3
    end,
    address asc

asc可省略

② 多个字段

先按照address排序，相同再按照name排序

select
    name, address
from
    tb_user
order by
    case address
        when 'China' then  1
        when 'America' then 2
        when 'Japan' then 3
    end,
    address asc,
    case name
        when '李四' then  1
        when '张三' then 2
        when 'jane' then 3
        when 'jack' then 4
    end,
    name desc

三、随机排序

使用rand()函数随机排序，不根据任何字段，每次的顺序都是随机的

select
    name, address
from
    tb_user
order by
    rand()