mysql order by instr_ORDER BY 后为什么能用INSTR来自定义排序规则原理是什么

首先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 |

+------------------------------------------------------------+