基于MyBatis插件实现动态表名解决多环境单一数据库问题

业务场景

在为某新能源汽车厂商进行我司系统私有化部署时，在预演环境和生产环境中，客户仅提供了一个 MySQL 数据库实例。为了确保数据隔离并避免不同环境之间的数据冲突，常规做法是为每个环境创建独立的表（如通过添加环境前缀或后缀）。
然而，如果每次切换环境都需要手动修改 SQL 或配置文件中的表名，不仅效率低下，而且容易出错。为此，小编利用 MyBatis 插件机制 实现了动态表名替换，从而优雅地解决了多环境共用数据库的问题。

解决方案

/*** 动态表名工具参数** @author 言安* @date 2025/3/20 14:37*/
@Data
@Component
@ConfigurationProperties(prefix = SpongeDynamicTableNameProperties.PREFIX)
public class SpongeDynamicTableNameProperties {public static final String PREFIX = "sponge.dynamic";/*** 是否开启*/private Boolean enable = Boolean.FALSE;/*** 默认表名后缀*/private String defaultTableNameSuffix;/*** 表名后缀映射（key:表名, value: 后缀）* 优先取表名后缀映射，取不到取 defaultTableNameSuffix*/private Map<String, String> tableNameSuffixMap;
}

/*** 动态表名处理器** @author 言安* @date 2025/3/20 13:41*/
@Component
public class SpongeDynamicTableNameHandler implements TableNameHandler {// 默认后缀private final static String DEFAULT_SUFFIX = "_test";@Resourceprivate SpongeDynamicTableNameProperties spongeDynamicTableNameProperties;@Overridepublic String dynamicTableName(String sql, String tableName) {String suffix = DEFAULT_SUFFIX;if (spongeDynamicTableNameProperties != null) {if (spongeDynamicTableNameProperties.getDefaultTableNameSuffix() != null) {suffix = spongeDynamicTableNameProperties.getDefaultTableNameSuffix();}if (spongeDynamicTableNameProperties.getTableNameSuffixMap() != null && spongeDynamicTableNameProperties.getTableNameSuffixMap().containsKey(tableName)) {suffix = spongeDynamicTableNameProperties.getTableNameSuffixMap().get(tableName);}}return StringUtil.isBlank(suffix) ? tableName : tableName + suffix;}
}

/*** MybatisPlus配置类** @author 言安* @date 2025/3/20 13:41*/
@Configuration
@EnableTransactionManagement
public class MybatisPlusConfiguration {@Resourceprivate SpongeDynamicTableNameProperties spongeDynamicTableNameProperties;@Resourceprivate SpongeDynamicTableNameHandler myTableNameHandler;@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();if (spongeDynamicTableNameProperties != null && Boolean.TRUE.equals(spongeDynamicTableNameProperties.getEnable())) {// 动态表名插件DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();dynamicTableNameInnerInterceptor.setTableNameHandler(myTableNameHandler);interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);}interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));return interceptor;}
}