iBatis/MyBatis 批量更新含多值字段：獎項字段的增減操作實戰

業務場景

學生表 student 包含字段 awards，儲存格式為逗號分隔字串（如 "1,3,5"），對應獎項表 award_dict 的 id。需求：批量為多位學生新增或移除某個獎項，每位學生操作可能不同。

核心挑戰

• 字段值為非正規化的逗號分隔字串
• 需避免重複新增、避免刪除不存在的獎項
• 批量操作需高效、支援事務

SQL 方案（以 SQL Server 為例）

1. 新增獎項

<update id="batchAddAward" parameterType="map">
    UPDATE student
    SET awards = awards + ',' + #{awardId}
    WHERE id IN
    <foreach collection="studentIds" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
    AND ',' + awards + ',' NOT LIKE '%,' + #{awardId} + ',%'
</update>

2. 移除獎項

<update id="batchRemoveAward" parameterType="map">
    UPDATE student
    SET awards = 
        CASE 
            WHEN awards = #{awardId} THEN ''
            ELSE SUBSTRING(
                REPLACE(',' + awards + ',', ',' + #{awardId} + ',', ','),
                2, 
                LEN(awards) - LEN(#{awardId}) - 
                CASE WHEN awards = #{awardId} THEN 0 ELSE 1 END
            )
        END
    WHERE id IN
    <foreach collection="studentIds" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
    AND ',' + awards + ',' LIKE '%,' + #{awardId} + ',%'
</update>

3. 替換獎項（舊→新）

<update id="batchReplaceAward" parameterType="map">
    UPDATE student
    SET awards = SUBSTRING(
        REPLACE(',' + awards + ',', ',' + #{oldAwardId} + ',', ',' + #{newAwardId} + ','),
        2,
        LEN(awards) - LEN(#{oldAwardId}) + LEN(#{newAwardId})
    )
    WHERE id IN
    <foreach collection="studentIds" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
    AND ',' + awards + ',' LIKE '%,' + #{oldAwardId} + ',%'
</update>

關鍵技巧解析

Java 服務層調用範例

@Transactional
public void batchAddAward(List studentIds, String awardId) {
    Map params = new HashMap<>();
    params.put("studentIds", studentIds);
    params.put("awardId", awardId);
    studentMapper.batchAddAward(params);
}

架構建議：長期演進

雖然上述方案能解決當前問題，但強烈建議重構為關聯表設計：

CREATE TABLE student_award (
    student_id BIGINT NOT NULL,
    award_id   BIGINT NOT NULL,
    PRIMARY KEY (student_id, award_id),
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (award_id) REFERENCES award_dict(id)
);

優勢：

• 符合資料庫正規化，查詢統計極其高效
• 避免字串操作的各種邊界 Bug
• 支援獎項屬性擴充（如獲得時間、等級）
• MyBatis 關聯映射 <collection> 原生支援

遷移腳本參考

-- 一次性將逗號字串拆解寫入關聯表
INSERT INTO student_award (student_id, award_id)
SELECT id, CAST(value AS UNSIGNED)
FROM student
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(awards, ',', '","'), '"]'),
    "$[*]" COLUMNS (value VARCHAR(10) PATH "$")
) AS jt
WHERE awards IS NOT NULL AND awards != '';