如何在MyBatis中实现PostgreSQL JSONB字段多计数器递增？

问题根源 #

你遇到的错误是因为MyBatis生成的SQL中，column_1被多次写在SET子句里（每个<if>标签都生成一次column_1 = ...），而PostgreSQL不允许同一列在同一条UPDATE语句中被多次赋值。

核心解决方案：嵌套jsonb_set调用 #

jsonb_set的返回值是更新后的JSONB对象，因此可以把多个字段的更新操作链式嵌套，这样整个UPDATE语句里只需要给column_1赋值一次。结合MyBatis的动态SQL标签，就能实现根据参数动态更新指定的JSONB字段。

针对两个字段的简洁实现 #

如果只需要处理key_alpha和key_beta两个字段，用<choose>标签分支处理所有可能的参数组合即可：

UPDATE table_a
SET column_1 = 
<choose>
  <!-- 同时更新两个字段：嵌套jsonb_set，先更新alpha再用结果更新beta -->
  <when test="incrementCountAlpha != null and incrementCountBeta != null">
    jsonb_set(
      jsonb_set(column_1, '{key_alpha}', to_jsonb((column_1->>'key_alpha')::bigint + #{incrementCountAlpha})),
      '{key_beta}', to_jsonb((column_1->>'key_beta')::bigint + #{incrementCountBeta})
    )
  </when>
  <!-- 只更新alpha -->
  <when test="incrementCountAlpha != null">
    jsonb_set(column_1, '{key_alpha}', to_jsonb((column_1->>'key_alpha')::bigint + #{incrementCountAlpha}))
  </when>
  <!-- 只更新beta -->
  <when test="incrementCountBeta != null">
    jsonb_set(column_1, '{key_beta}', to_jsonb((column_1->>'key_beta')::bigint + #{incrementCountBeta}))
  </when>
  <!-- 无更新参数时，保持原字段不变 -->
  <otherwise>column_1</otherwise>
</choose>
WHERE id = #{id} <!-- 务必加上WHERE条件定位目标行，避免全表更新 -->

支持更多字段的灵活实现 #

如果后续要扩展更多JSONB字段，可以用<trim>标签动态拼接嵌套的jsonb_set结构，避免写大量分支：

UPDATE table_a
SET column_1 = 
<trim prefix="jsonb_set(" suffix=")" suffixOverrides=",">
  <!-- 基础值：如果有多个更新，第一个jsonb_set的输入是原column_1，后续是前一个jsonb_set的结果 -->
  <if test="incrementCountAlpha != null or incrementCountBeta != null">
    <choose>
      <when test="incrementCountAlpha != null">
        jsonb_set(column_1, '{key_alpha}', to_jsonb((column_1->>'key_alpha')::bigint + #{incrementCountAlpha}))
      </when>
      <otherwise>column_1</otherwise>
    </choose>
    <!-- 拼接第二个字段的更新 -->
    <if test="incrementCountBeta != null">
      , '{key_beta}', to_jsonb((column_1->>'key_beta')::bigint + #{incrementCountBeta})
    </if>
    <!-- 可继续添加更多字段的<if>标签，格式同上 -->
  </if>
</trim>
WHERE id = #{id}

性能优先的替代方案 #

如果这些JSONB字段的更新频率极高，且业务允许调整表结构，将JSONB中的独立字段拆分到单独的数据库列是性能最优的选择：

• 把key_alpha和key_beta拆成单独的BIGINT列，更新操作直接写成普通的列赋值，无需JSONB函数调用，数据库执行效率更高。
• 可以为这些单独列创建索引，进一步提升查询和更新的性能。

如果无法修改表结构，还可以做以下优化：

• 确保UPDATE语句带有精准的WHERE条件，避免全表扫描。
• 对JSONB中频繁查询/更新的key创建GIN索引，例如：

  CREATE INDEX idx_table_a_column_1_keys ON table_a USING GIN ((column_1 -> 'key_alpha'), (column_1 -> 'key_beta'));
  

  注意：索引会增加更新的开销，需要根据业务读写比例权衡是否创建。

内容的提问来源于stack exchange，提问作者m4mmt