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

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.