动态sql出错，黑马头条sql

Question

在学习黑马头条这一项目时 出现报错：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 12
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 12

望解答

 <select id="loadArticleList" resultMap="resultMap">
        SELECT
        *
        FROM
        `ap_article` aa
        LEFT JOIN ap_article_config aac ON aa.id = aac.article_id
        <where>
            and aac.is_delete != 1
            and aac.is_down != 1
            <!-- loadmore -->
            <if test="type != null and type == 1">
                and aa.publish_time <![CDATA[<]]> #{dto.minBehotTime}
            </if>
            <if test="type != null and type == 2">
                and aa.publish_time <![CDATA[>]]> #{dto.maxBehotTime}
            </if>
            <if test="dto.tag != '__all__'">
                and aa.channel_id = #{dto.tag}
            </if>
        </where>
        order by aa.publish_time desc
        LIMIT #{dto.size}
    </select>

Answer 1

以下回复参考：皆我百晓生、券券喵儿等免费微信小程序作答:

这个错误是由于SQL语句中的"null"导致的。在这段SQL语句中，可能的原因是<where>标签中的判断条件没有满足，导致生成的SQL语句中包含了"null"。

为了解决这个问题，你可以在<where>标签中添加一个默认的条件，以确保至少有一个条件会被满足。例如，你可以添加一个恒成立的条件，如下所示：

<select id="loadArticleList" resultMap="resultMap">
    SELECT
    *
    FROM
    `ap_article` aa
    LEFT JOIN ap_article_config aac ON aa.id = aac.article_id
    <where>
        1=1
        and aac.is_delete != 1
        and aac.is_down != 1
        <!-- loadmore -->
        <if test="type != null and type == 1">
            and aa.publish_time <![CDATA[<]]> #{dto.minBehotTime}
        </if>
        <if test="type != null and type == 2">
            and aa.publish_time <![CDATA[>]]> #{dto.maxBehotTime}
        </if>
        <if test="dto.tag != '__all__'">
            and aa.channel_id = #{dto.tag}
        </if>
    </where>
    order by aa.publish_time desc
    LIMIT #{dto.size}
</select>

这样，即使其他的条件没有被满足，"1=1"这个条件也会被包含在生成的SQL语句中，从而避免了"null"的出现。