1 主键处理：
主键字段，mybatis中不可以传递null，在insert 中去嗲主键ID的插入；
2 唯一性校验的处理
mysql: 不需要指定唯一条件，会根据插入结果判断
<update id=\"updateSequenceDetail\" >
INSERT INTO erp_sequence_detail (sequence_id, period_year, period_month, current_number)
VALUES (#{sequenceId}, #{periodYear}, #{periodMonth}, #{currentNumber})
ON DUPLICATE KEY UPDATE
current_number = #{currentNumber};
</update>
pg: 需要传递唯一约束
--1 使用约束名
ON CONFLICT ON CONSTRAINT uk_seq_detail
-- 2 使用约束字段
ON CONFLICT (sequence_id, period_year, period_month)
修改后的sql:
<update id=\"updateSequenceDetail\" >
INSERT INTO erp_sequence_detail (sequence_id, period_year, period_month, current_number)
VALUES (#{sequenceId}, #{periodYear}, #{periodMonth}, #{currentNumber})
ON CONFLICT (sequence_id, period_year, period_month)
DO UPDATE SET current_number = #{currentNumber}
</update>
3 pg添加唯一索引
-- 创建约束时指定名称
ALTER TABLE erp_sequence_detail
ADD CONSTRAINT uk_seq_detail
UNIQUE (sequence_id, period_year, period_month);
4 批量更新的写法
mysql:
<update id=\"batchUpsertLocationByStockIn\">
UPDATE erp_warehouse_location AS e
JOIN (
<foreach collection=\"subList\" item=\"item\" separator=\"UNION ALL\">
SELECT #{item.locationId} AS location_id,
#{item.tenantId} AS tenant_id,
#{item.skuId} AS sku_id,
#{item.quantity} AS change_quantity
FROM DUAL
</foreach>
) AS t
ON e.location_id = t.location_id and e.tenant_id = t.tenant_id and e.sku_id = t.sku_id
SET
-- 修改总库存，允许数量为负数
e.quantity = e.quantity + t.change_quantity;
</update>
pg库：
<update id=\"batchUpsertLocationByStockIn\">
UPDATE erp_warehouse_location AS e
SET quantity = e.quantity + t.change_quantity
FROM (
VALUES
<foreach collection=\"subList\" item=\"item\" separator=\",\">
(#{item.locationId}, #{item.tenantId}, #{item.skuId}, #{item.quantity})
</foreach>
) AS t(location_id, tenant_id, sku_id, change_quantity)
WHERE e.location_id = t.location_id
AND e.tenant_id = t.tenant_id
AND e.sku_id = t.sku_id
</update>
5 find_in_set 不支持
mysql:
<update id=\"updateDeptChildrenStatus\">
update sys_dept set status = #{status} where find_in_set(#{deptId}, ancestors) and tenant_id = #{tenantId}
</update>
pg库：
<update id=\"updateDeptChildrenStatus\">
UPDATE sys_dept
SET status = #{status}
WHERE ancestors LIKE \'%\' || #{deptId} || \'%\'
AND tenant_id = #{tenantId}
</update>
<update id=\"updateDeptChildrenStatus\">
UPDATE sys_dept
SET status = #{status}
WHERE ancestors ~ (\'(^|,)\' || #{deptId} || \'(,|$)\')
AND tenant_id = #{tenantId}
</update>
<update id=\"updateDeptChildrenStatus\">
UPDATE sys_dept
SET status = #{status}
WHERE #{deptId} = ANY(string_to_array(ancestors, \',\'))
AND tenant_id = #{tenantId}
</update>
