MySQL 保持select in中的顺序

在进行数据查询是 我传入的参数是(1,2,3,4,27,5)，使用select in来查询发现返回的结果是按照（1,2,3,4,5,27）返回的

select in 会将id进行升序排列后返回

SELECT * FROM jd.commodity where jd.commodity.commodity_id in (1,2,3,4,27,5);

 想要保持输入的顺序可以做下面这个处理：

SELECT * FROM jd.commodity where jd.commodity.commodity_id in (1,2,3,4,27,5) order by field(jd.commodity.commodity_id,1,2,3,4,27,5);

在mapper映射中使用：

 

<select id="XXXXX" resultType="XXXXX">
            select commodity_id as commodityId , commodity_name as commodityName , commodity_price as commodityPrice , commodity_img as commodityImg
            from commodity where commodity_id  in
            <foreach collection="commodityIdList" item="commodityId" index="index" open="(" separator="," close=")">
                #{commodityId}
            </foreach>
            order by field(commodity_id,
            <foreach collection="commodityIdList" item="commodityId" index="index"  separator="," >
                     ${commodityId}
            </foreach>);
    </select>>

参考：xxxMapper.xml中编写sql语句时有order by的注意点_mapper order by_快点懂java的博客-CSDN博客