MySQLIntegrityConstraintViolationException: Column 'ID' in where clause is ambiguous-优快云博客

本文介绍了在使用MyBatis进行多表联查时遇到的字段歧义问题，特别是当两个表中含有相同字段名时如何明确指定查询目标，通过一个具体的例子展示了如何修改SQL语句来解决此问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQLIntegrityConstraintViolationException: Column ‘ID’ in where clause is ambiguous

原文地址：http://blog.youkuaiyun.com/u011731544/article/details/78986593

Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘ID’ in where clause is ambiguous

The error may exist in file [E:\1715\ssm-blog-admin\target\ssm-blog-admin\WEB-INF\classes\com\ssm\blog\dao\mapper\BlogMappper.xml]

The error may involve com.ssm.blog.dao.BlogDAO.getBlogList-Inline

The error occurred while setting parameters

SQL: SELECT count(0) FROM BLOG B INNER JOIN ADMIN_USER A ON B.USER_ID = A.ID LEFT JOIN CATEGORY C ON B.CATEGORY_ID = C.ID WHERE ID = ?

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
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:85)

意思是sql语句中ID这个字段有歧义，这种情况多出现在多表联查的时候，两个表中有同名的字段，比如ID，而查询的时候没有指定是哪张表的ID,下面的语句会报错。因为BLOG和ADMIN_USER表中都有ID,WHERE中的ID=?没有指明去查询谁的ID

SELECT count(0) FROM BLOG B INNER JOIN ADMIN_USER A ON B.USER_ID = A.ID LEFT JOIN CATEGORY C ON B.CATEGORY_ID = C.ID WHERE ID = ?

修改后：

SELECT count(0) FROM BLOG B INNER JOIN ADMIN_USER A ON B.USER_ID = A.ID LEFT JOIN CATEGORY C ON B.CATEGORY_ID = C.ID WHERE B.ID = ?