PostgreSql 项目实战 4 -mysql迁移PG库 主键、唯一处理、批量修改

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库：

<!-- 1 字符串包含判断 -->
<update id="updateDeptChildrenStatus">
    UPDATE sys_dept 
    SET status = #{status} 
    WHERE ancestors LIKE '%' || #{deptId} || '%' 
      AND tenant_id = #{tenantId}
</update>

<!-- 2 正则包含判断 -->
<update id="updateDeptChildrenStatus">
    UPDATE sys_dept 
    SET status = #{status} 
    WHERE ancestors ~ ('(^|,)' || #{deptId} || '(,|$)')
      AND tenant_id = #{tenantId}
</update>

<!-- 3 最精确，需要标准逗号分隔 -->
<update id="updateDeptChildrenStatus">
    UPDATE sys_dept 
    SET status = #{status} 
    WHERE #{deptId}::text = ANY(string_to_array(ancestors, ','))
      AND tenant_id = #{tenantId}
</update>

6 布尔类型处理

-- 1 备份数据
CREATE TABLE sys_role_temp AS SELECT * FROM sys_role;

select * from sys_role_temp;


-- 2. 创建临时布尔列
ALTER TABLE sys_role 
ADD COLUMN menu_check_strictly_bool BOOLEAN DEFAULT true;

ALTER TABLE sys_role 
ADD COLUMN dept_check_strictly_bool BOOLEAN DEFAULT true;

-- 3. 迁移数据到新列
UPDATE sys_role 
SET 
    menu_check_strictly_bool = (menu_check_strictly = 1),
    dept_check_strictly_bool = (dept_check_strictly = 1);
    
-- 4. 验证数据迁移
SELECT 
    role_id,
    menu_check_strictly as old_menu,
    menu_check_strictly_bool as new_menu,
    dept_check_strictly as old_dept,
    dept_check_strictly_bool as new_dept
FROM sys_role 

-- 5. 删除旧列
ALTER TABLE sys_role 
DROP COLUMN menu_check_strictly;

ALTER TABLE sys_role 
DROP COLUMN dept_check_strictly;

-- 6. 重命名新列
ALTER TABLE sys_role 
RENAME COLUMN menu_check_strictly_bool TO menu_check_strictly;

ALTER TABLE sys_role 
RENAME COLUMN dept_check_strictly_bool TO dept_check_strictly;

-- 7. 添加注释
COMMENT ON COLUMN sys_role.menu_check_strictly IS '菜单树选择项是否关联显示（0-父子不互相关联显示 1-父子互相关联显示）';
COMMENT ON COLUMN sys_role.dept_check_strictly IS '部门树选择项是否关联显示（0-父子不互相关联显示 1-父子互相关联显示）';