mybatis多表联查没有使用起别名

MyBatis 多表联查时如果没有使用别名，会产生多种错误。下面详细说明这些错误情况和具体示例。

常见的错误类型
1. 查询结果映射错误（最常见）
错误示例
xml
<!-- 没有使用别名的情况 -->
<resultMap id="UserOrderMap" type="User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="email" column="email"/>
    
    <association property="order" javaType="Order">
        <id property="id" column="id"/>          <!-- 冲突！ -->
        <result property="orderNo" column="order_no"/>
    </association>
</resultMap>

<select id="selectUserWithOrder" resultMap="UserOrderMap">
    SELECT 
        u.id,           <!-- users表的id -->
        u.name, 
        u.email,
        o.id,           <!-- orders表的id，列名冲突！ -->
        o.order_no
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{id}
</select>
产生的错误现象
数据错乱：Order 的 id 可能会覆盖 User 的 id

数据丢失：其中一个表的 id 会被另一个覆盖

没有报错，但数据不正确，很难排查

java
// 实际运行结果可能：
User{id=1001, name='张三', email='zhang@example.com'} // id 应该是用户的id
Order{id=1001, orderNo='ORDER001'} // 但order的id也变成了1001，实际上可能是2001
2. 结果集映射异常
错误信息示例
text
### Cause: org.apache.ibatis.executor.ResultMapException: 
Error attempting to get column 'id' from result set.  
Cause: java.sql.SQLException: 列名 'id' 不明确
或者：

text
### Error querying database.  Cause: java.sql.SQLException: 
Ambiguous column name 'id'
3. 数据库层面的列名不明确错误
不同的数据库有不同的错误信息：

MySQL:

text
SQLException: Column 'id' in field list is ambiguous
PostgreSQL:

text
SQLException: Column reference "id" is ambiguous
Oracle:

text
SQLException: ORA-00918: column ambiguously defined
SQL Server:

text
SQLException: Ambiguous column name 'id'
具体场景分析
场景1：相同列名冲突
sql
-- 错误SQL
SELECT u.id, u.name, o.id, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 执行时报错：Column 'id' in field list is ambiguous
场景2：ResultMap 映射失败
xml
<!-- 即使SQL能执行，ResultMap也会出错 -->
<select id="getUserOrder" resultMap="userOrderMap">
    SELECT u.*, o.*  -- 这是非常危险的做法！
    FROM users u 
    JOIN orders o ON u.id = o.user_id
</select>
这种情况下：

可能不报错，但数据映射完全错误

User 对象的属性可能被 Order 对象的同名字段覆盖

解决方案
方案1：为所有列设置别名
xml
<select id="selectUserWithOrder" resultMap="UserOrderMap">
    SELECT 
        u.id as user_id,
        u.name as user_name,
        u.email as user_email,
        o.id as order_id,
        o.order_no as order_no,
        o.create_time as order_create_time
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{id}
</select>

<resultMap id="UserOrderMap" type="User">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
    <result property="email" column="user_email"/>
    
    <association property="order" javaType="Order">
        <id property="id" column="order_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="createTime" column="order_create_time"/>
    </association>
</resultMap>
方案2：使用自动映射（不推荐用于复杂查询）
xml
<!-- 使用auto-mapping，但仍有风险 -->
<select id="selectUserWithOrder" resultType="map">
    SELECT 
        u.id as user_id,
        u.name as user_name,
        o.id as order_id,
        o.order_no
    FROM users u
    JOIN orders o ON u.id = o.user_id
</select>
方案3：使用列前缀配置
xml
<resultMap id="UserOrderMap" type="User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="email" column="email"/>
    
    <association property="order" resultMap="orderMap" columnPrefix="order_"/>
</resultMap>

<resultMap id="orderMap" type="Order">
    <id property="id" column="id"/>
    <result property="orderNo" column="order_no"/>
    <result property="createTime" column="create_time"/>
</resultMap>

<select id="selectUserWithOrder" resultMap="UserOrderMap">
    SELECT 
        u.id,
        u.name,
        u.email,
        o.id as order_id,           <!-- 前缀会自动移除 -->
        o.order_no as order_order_no,
        o.create_time as order_create_time
    FROM users u
    JOIN orders o ON u.id = o.user_id
</select>
实际错误案例重现
错误代码示例
java
// Mapper接口
public interface UserMapper {
    UserOrderDTO getUserWithOrder(Long userId);
}

// DTO类
@Data
public class UserOrderDTO {
    private Long userId;
    private String userName;
    private Long orderId;
    private String orderNo;
}

// XML配置（错误的写法）
<select id="getUserWithOrder" resultType="UserOrderDTO">
    SELECT 
        u.id,        -- 冲突！
        u.name, 
        o.id,        -- 冲突！
        o.order_no
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{userId}
</select>
运行时的错误信息
text
### SQL: SELECT u.id, u.name, o.id, o.order_no FROM users u JOIN orders o ON u.id = o.user_id WHERE u.id = ?
### Cause: java.sql.SQLException: Column 'id' in field list is ambiguous
; bad SQL grammar []; 
nested exception is java.sql.SQLException: Column 'id' in field list is ambiguous
最佳实践建议
始终为关联查询的列设置别名

使用统一的别名命名规范，如：表名_列名 或 表别名_列名

避免使用 SELECT *，明确列出需要的列

在 ResultMap 中明确指定所有映射关系

为复杂的多表查询创建专门的 DTO/VO 类

xml
<!-- 推荐的做法 -->
<select id="getUserWithOrder" resultMap="UserOrderResultMap">
    SELECT 
        u.id as user_id,
        u.name as user_name,
        u.email as user_email,
        o.id as order_id,
        o.order_no as order_no,
        o.amount as order_amount,
        o.create_time as order_create_time
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{userId}
</select>