软件开发技术分享
0%

MySQL 查询结果集按指定顺序排序（自定义排序）

发表于 分类于
本文字数： 3.7k 阅读时长 ≈ 3 分钟
平时写项目经常需要从数据库（MySQL）中查询数据，而且需要以一种奇怪的排序方式去展示，这时我们可以用 FIELD() 和 FIND_IN_SET() 函数来实现排序。

平时在写项目时，经常需要从 MyuSQL 中查询一些数据，偶尔还需要将数据以一种难以描述的排序方式展示。对于数据量不大的项目、或是个人项目我们可以使用 FIELD() 和 FIND_IN_SET() 函数来实现自定义排序。

1. 准备工作

先创建一张测试表，并随意插入一些数据（点击查看 SQL 语句） 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE `uri_wang_vehicle_mgmt`  (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '类型',
    `brand` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '品牌',
    `create_time` bigint(20) NOT NULL DEFAULT 0 COMMENT '插入时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '车辆管理';

INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('自行车', '凤凰', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('摩托车', '豪爵', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('小汽车', '比亚迪', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('电动自动车', '雅迪', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('面包车', '五菱', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('小汽车', '吉利', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('皮卡', '五菱', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('拖拉机', '东方红', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('电动自行车', '爱玛', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('电动自行车', '九号', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('摩托车', '宗申', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('拖拉机', '沃德', UNIX_TIMESTAMP());
INSERT INTO `uri_wang_vehicle_mgmt` (`type`, `brand`, `create_time`) VALUES ('自行车', '捷安特', UNIX_TIMESTAMP());

2. FIELD() 和 FIND_IN_SET() 函数的使用

先了解一下函数的功能，通过下面的测试，基本可以知道这两个函数的功能是返回参数1的索引，索引从1开始计算。具体使用方法请自行阅读博客末尾的引用文献。

1
2
3
4
5
6
7
8
9
10
11
SELECT FIELD('自行车', '自行车', '摩托车', '电动自行车', '小汽车', '面包车', '拖拉机', '皮卡');
-- 执行结果：1

SELECT FIELD('小汽车', '自行车', '摩托车', '电动自行车', '小汽车', '面包车', '拖拉机', '皮卡');
-- 执行结果：4

SELECT FIND_IN_SET('自行车', '自行车,摩托车,电动自行车,小汽车,面包车,拖拉机,皮卡');
-- 执行结果：1

SELECT FIND_IN_SET('小汽车', '自行车,摩托车,电动自行车,小汽车,面包车,拖拉机,皮卡')
-- 执行结果：4

查询所有车辆信息，并按照类型、品牌排序，类型按照自行车、摩托车、电动自行车、小汽车、面包车、拖拉机、皮卡顺序排序。

方法一，使用 FIELD() 函数实现自定义排序：

1
2
3
4
5
6
7
8
9
10
11
SELECT
    uri_wang_vehicle_mgmt.id,
    uri_wang_vehicle_mgmt.type,
    uri_wang_vehicle_mgmt.brand,
    FROM_UNIXTIME(uri_wang_vehicle_mgmt.create_time, '%Y-%m-%d') AS create_time
FROM
    uri_wang_vehicle_mgmt
ORDER BY
    FIELD(uri_wang_vehicle_mgmt.type, '自行车', '摩托车', '电动自行车', '小汽车', '面包车', '拖拉机', '皮卡'),
    uri_wang_vehicle_mgmt.brand
;

方法二，使用 FIND_IN_SET() 函数实现自定义排序：

1
2
3
4
5
6
7
8
9
10
11
SELECT
    uri_wang_vehicle_mgmt.id,
    uri_wang_vehicle_mgmt.type,
    uri_wang_vehicle_mgmt.brand,
    FROM_UNIXTIME(uri_wang_vehicle_mgmt.create_time, '%Y-%m-%d') AS create_time
FROM
    uri_wang_vehicle_mgmt
ORDER BY
    FIND_IN_SET(uri_wang_vehicle_mgmt.type, '自行车,摩托车,电动自行车,小汽车,面包车,拖拉机,皮卡'),
    uri_wang_vehicle_mgmt.brand
;

MySQL :: MySQL 5.7 Reference Manual :: 12.8 String Functions FIELD
MySQL :: MySQL 5.7 Reference Manual :: 12.8 String Functions FIND_IN_SET

相关文章