Mybatis foreach拼接in sql 时，避免数据误伤、SQL异常的笔记

出现的问题点

    <select id="querySaDataByHour" resultMap="sendLogicAreaInfo">
        select
        time,
        eci,
        coalesce(rrc_succ_conn_rate,0) as rrc_succ_conn_rate
        from end2end_pm_sa_cudu_common_hour_${dt}
        <where>
            and is_delete = 0
            and cgi in
            <foreach collection="cgiArr" item="cgi" open="(" close=")" separator=",">
                #{cgi}
            </foreach>
        </where>
        order by time
    </select>

上面的写法，如果传入的cgiArr输入为空，会出现一个非常尴尬的一个sql错误

解决办法

方法一

修改为：

    <select id="querySaDataByHour" resultMap="sendLogicAreaInfo">
        select
        time,
        eci,
        coalesce(rrc_succ_conn_rate,0) as rrc_succ_conn_rate
        from end2end_pm_sa_cudu_common_hour_${dt}
        <where>
            and is_delete = 0
            and cgi in(-1,
            <foreach collection="cgiArr" item="cgi" open="," separator=",">
                #{cgi}
            </foreach>
            )
        </where>
        order by time
    </select>

这种类似于where 1=1 的写法

方法二

添加if 判断

    <select id="querySaDataByHour" resultMap="sendLogicAreaInfo">
        select
        time,
        eci,
        coalesce(rrc_succ_conn_rate,0) as rrc_succ_conn_rate
        from end2end_pm_sa_cudu_common_hour_${dt}
        <where>
            and is_delete = 0
            <if test="cgiArr!=null and cgiArr.size > 0">
                and cgi in
                <foreach collection="cgiArr" item="cgi" open="(" close=")" separator=",">
                    #{cgi}
                </foreach>
            </if>
        </where>
        order by time
    </select>

两种方法都可以，具体业务看解决吧

ps:程序逻辑肯定要判空的，这种写法会兜底，避免出现拼接异常