MySQL自定义排序ORDER BY FIELD()

1、创建测试数据表

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `tasks`; CREATE TABLE `tasks` ( `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `name` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `state` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `execute_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;

2、自定义排序sql

SELECT * FROM `tasks` order by FIELD(state, 'ready', 'failure', 'success'), execute_time desc

说明： order by FIELD(state, 'ready', 'failure', 'success')，mysql自定义排序，按照ready、failure、success进行自定义排序。

3、测试结果：