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

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 |
+------------------------------------------------------------+

Answer 2

order by只是一个子句，instr是一个内部函数，用在你所列举的例子中完全是作为order by子句的clause，语法上就是这么定义的，如果clause是表达式，先计算表达式，再根据表达式结果来执行order by.