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

Question

今天碰到个问题，需要用到自定义排序 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;

表数据：

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 的排序排在了前面？

Answer 1

FIELD

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list.
Returns 0 if str is not found.

If all arguments to FIELD() are strings, all arguments are compared as
strings. If all arguments are numbers, they are compared as numbers.
Otherwise, the arguments are compared as double.

If str is NULL, the return value is 0 because NULL fails equality
comparison with any value. FIELD() is the complement of ELT().

mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    -> 2
mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    -> 0

所以，

FIELD(1, 1, 2) --> 1
FIELD(2, 1, 2) --> 2
FILED(3, 1, 2) --> 0

这一列没有 DESC 所以按升序排，正好这个结果。