问题背景
在实际业务中，我们常会遇到数据冗余问题。例如，一个公司表（sys_company）中存在多条相同公司名的记录，但只有一条有效（del_flag=0），其余需要删除。删除前需将关联表（如合同草稿表、发票表等）的外键字段（如purchaser_id）替换为保留记录的ID。这类问题通常涉及多表、多字段的动态更新，如何高效且安全地实现？
解决方案
我们将通过以下步骤实现：
- 配置化驱动：用配置类声明需要处理的表和字段，避免硬编码。
- 动态SQL更新：通过MyBatis XML实现批量更新和删除。
- 事务一致性：确保所有操作原子化执行。
实现步骤
1. 定义实体类
CompanyRetainedInfo：封装需保留的公司信息
import lombok.Data;@Data
public class CompanyRetainedInfo {private String companyName; // 公司名称private Long retainedId; // 需保留的公司ID（del_flag=0的记录）private String retainedName; // 需保留的公司名称（与companyName一致）
}
• 作用：映射查询结果，传递保留记录的ID和名称。
• Lombok：@Data 自动生成Getter/Setter和toString()方法。
2. 定义配置类
TableConfig：声明需处理的表和外键关系
public class TableConfig {private String tableName; // 表名（如contract_draft）private String idColumn; // 外键ID字段（如purchaser_id）private String nameColumn; // 名称字段（如purchaser_name，可能为null）// 构造器 + Getter/Setterpublic TableConfig(String tableName, String idColumn, String nameColumn) {this.tableName = tableName;this.idColumn = idColumn;this.nameColumn = nameColumn;}
}
3. 编写MyBatis Mapper接口
CompanyCleanMapper：定义数据操作接口（无注解，纯XML映射）
@Mapper
public interface CompanyCleanMapper {// 查询需保留的公司信息（del_flag=0）List<CompanyRetainedInfo> selectRetainedCompanies();// 根据公司名查询待删除的ID列表（del_flag!=0）List<Long> selectIdsToDelete(String companyName);// 更新关联表的外键引用void updateForeignKeys(@Param("config") TableConfig config,@Param("retainedId") Long retainedId,@Param("retainedName") String retainedName,@Param("ids") List<Long> ids);// 删除冗余公司记录void deleteCompanies(@Param("ids") List<Long> ids);
}
4. 实现XML映射文件
CompanyCleanMapper.xml：定义动态SQL逻辑
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.CompanyCleanMapper"><!-- 查询需保留的公司 --><select id="selectRetainedCompanies" resultType="CompanyRetainedInfo">SELECT company_name AS companyName, id AS retainedId, company_name AS retainedNameFROM sys_companyWHERE del_flag = 0AND company_name IN (SELECT company_nameFROM sys_companyGROUP BY company_nameHAVING COUNT(*) > 1 AND SUM(del_flag = 0) = 1)</select><!-- 查询待删除的ID列表 --><select id="selectIdsToDelete" resultType="long">SELECT idFROM sys_companyWHERE company_name = #{companyName}AND del_flag != 0</select><!-- 动态更新外键引用 --><update id="updateForeignKeys">UPDATE ${config.tableName}SET<choose><when test="config.nameColumn != null"><!-- 同时更新ID和名称字段 -->${config.idColumn} = #{retainedId},${config.nameColumn} = #{retainedName}</when><otherwise><!-- 仅更新ID字段 -->${config.idColumn} = #{retainedId}</otherwise></choose>WHERE${config.idColumn} IN<foreach item="id" collection="ids" open="(" separator="," close=")">#{id}</foreach></update><!-- 批量删除公司记录 --><delete id="deleteCompanies">DELETE FROM sys_companyWHERE id IN<foreach item="id" collection="ids" open="(" separator="," close=")">#{id}</foreach></delete></mapper>
5. 服务层实现
CompanyCleanService：配置化驱动批量处理
@Service
@RequiredArgsConstructor
public class CompanyCleanService {private final CompanyCleanMapper companyCleanMapper;// 配置需要处理的表和字段private static final List<TableConfig> TABLE_CONFIGS = Arrays.asList(new TableConfig("contract_draft", "purchaser_id", "purchaser_name"),new TableConfig("invoice", "company_id", "company_name")// 按需添加其他表...);@Transactionalpublic void cleanDuplicateCompanies() {// 1. 查询所有需保留的公司List<CompanyRetainedInfo> retainedCompanies = companyCleanMapper.selectRetainedCompanies();for (CompanyRetainedInfo info : retainedCompanies) {// 2. 查询待删除的ID列表List<Long> idsToDelete = companyCleanMapper.selectIdsToDelete(info.getCompanyName());if (!idsToDelete.isEmpty()) {// 3. 更新所有关联表的外键引用TABLE_CONFIGS.forEach(config -> companyCleanMapper.updateForeignKeys(config, info.getRetainedId(), info.getRetainedName(), idsToDelete));// 4. 删除冗余公司记录companyCleanMapper.deleteCompanies(idsToDelete);}}}
}
关键设计说明
-
实体类与数据映射
•CompanyRetainedInfo通过别名（AS retainedId）直接映射查询结果，避免额外转换。
•companyName和retainedName字段值相同，但保留后者以明确语义。 -
XML动态SQL优势
•<choose>：根据配置动态决定是否更新名称字段。
•<foreach>：自动展开ID列表为IN (id1, id2...)，支持批量操作。
•${}占位符：安全引用配置的表名和字段名（非用户输入，无注入风险）。 -
事务与性能优化
•@Transactional：保证“更新外键”和“删除公司”操作的原子性。
• 索引建议：对sys_company.company_name和关联表的外键字段添加索引。
总结
通过 实体类封装、配置化表关系 和 MyBatis动态SQL，我们实现了一套可扩展的多表数据清洗方案。这种模式的核心在于：
- 抽象变化部分：将表和字段的差异收敛到配置类中。
- 复用不变逻辑：批量更新和删除操作由统一服务驱动。
- 最小化侵入性：新增表只需修改配置，无需改动核心逻辑。
该方案适用于用户中心、商品系统等存在外键关联的冗余数据处理场景，读者可结合实际需求调整配置和SQL逻辑。
