ORDER BY 后为什么能用INSTR来自定义排序规则原理是什么

Question

例：

select * from list ORDER BY INSTR('4,2,3,1',lid);

以及比较常见的和 IN 配合使用：

SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509)  ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,','))

想问这样做的原理是什么？

Answer 1

首先INSTR（str, substr）是返回substr在str中的位置，若不存在，则返回0。
select * from list ORDER BY INSTR('4,2,3,1',lid);
使用ORDER BY INSTR时，如果lid不存在'4,2,3,1'中，则返回0,order by默认按递增排序，所以lid为4,2,3,1的会放到最后。
SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509) ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,','))
mysql> select INSTR(',443,419,431,440,420,414,509,',CONCAT(',',443,','));
+------------------------------------------------------------+
| INSTR(',443,419,431,440,420,414,509,',CONCAT(',',443,',')) |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select INSTR(',443,419,431,440,420,414,509,',CONCAT(',',419,','));
+------------------------------------------------------------+
| INSTR(',443,419,431,440,420,414,509,',CONCAT(',',419,',')) |
+------------------------------------------------------------+
| 5 |
+------------------------------------------------------------+