MySQLIntegrityConstraintViolationException: Column 'ID' in where clause is ambiguous ;

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'ID' in where clause is ambiguous

; SQL []; Column 'ID' in where clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'ID' in where clause is ambiguous

意思是WHERE子句中的列“id”是不明确的

出现的原因是在多表连接查询时，两个表拥有一样的字段名，mysql不明确你要查询的是哪个表的字段名

错误代码：

<select id="getDiaryList" parameterType="Diary" resultMap="diaryMap">
        SELECT D.ID,D.TITLE,D.USER_ID,D.CREATED_DATE,D.CONTENT,D.STATUS,A.NICKNAME
        FROM diary D
        INNER JOIN ADMIN_USER A ON D.USER_ID=A.ID
        <where>
            <if test="id !=null">ID=#{id}</if>
            <if test="title !=null and title!=''">and TITLE like concat('%',#{title},'%')</if>
            <if test="nickname !=null and nickname!=''">and NICKNAME like concat('%',#{nickname},'%')</if>
        </where>
    </select>

正确代码：

将if条件中的ID前加上表名

<select id="getDiaryList" parameterType="Diary" resultMap="diaryMap">
        SELECT D.ID,D.TITLE,D.USER_ID,D.CREATED_DATE,D.CONTENT,D.STATUS,A.NICKNAME
        FROM diary D
        INNER JOIN ADMIN_USER A ON D.USER_ID=A.ID
        <where>
            <if test="id !=null">D.ID=#{id}</if>
            <if test="title !=null and title!=''">and TITLE like concat('%',#{title},'%')</if>
            <if test="nickname !=null and nickname!=''">and NICKNAME like concat('%',#{nickname},'%')</if>
        </where>
    </select>