一个关于 MySQL 的自定义排序问题

[wuxi889]

今天碰到个问题，需要用到自定义排序 ORDER BY FIELD ()，但是排序后的结果跟我想象的有差异，贴出来让大佬们看看，帮我解惑一下。

CREATE TABLE platform ( id int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id', name varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称', state tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态', created_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间', updated_time datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间', deleted_time datetime(0) NULL DEFAULT NULL COMMENT '删除时间', PRIMARY KEY (loan_id) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '平台表' ROW_FORMAT = Dynamic;

---

-- Records of platform

---

INSERT INTO platform VALUES (1, '范德萨范德萨', 1, '2019-07-09 10:28:49', '2019-07-09 18:17:44', NULL); INSERT INTO platform VALUES (2, '存存发达的存', 1, '2019-07-09 15:15:49', '2019-07-09 18:18:55', NULL); INSERT INTO platform VALUES (3, '复活复活方和', 1, '2019-07-09 18:17:05', '2019-07-09 18:17:56', NULL);

---

查询语句：

SELECT id, name FROM platform WHERE state = 1 AND platform.deleted_time IS NULL ORDER BY FIELD( id, 1, 2 ), updated_time DESC

---

查询结果： id name 3 范德萨范德萨 1 存存发达的存 2 复活复活方和

按道理说 ORDER BY 应该是先按 FIELD 先排序 再按 updated_time，现在缺是 updated_time 排在了前面？

[ebingtel]

你把 3 也放进 ORDER BY FIELD( id, 1, 2 ,3)应该就没这个问题了……感觉是 ORDER BY FIELD 的副作用