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');

select
    name, address
from
    tb_user
order by
    field(address, 'china', 'america', 'japan') asc

select
    name, address
from
    tb_user
order by
    field(address, 'china', 'america', 'japan') asc,
    field(name, '李四', '张三', 'jane', 'jack') desc

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

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

select
    name, address
from
    tb_user
order by
    rand()