数云融合丨 一文让你弄懂多租户数据库设计⽅案

@Configuration@MapperScan("com.baomidou.mybatisplus.samples.tenant.mapper")public class MybatisPlusConfig {  /** * 新多租户插件配置,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存万一出现问题 */ @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() { @Override public Expression getTenantId() { return new LongValue(1); }  // 这是 default 方法,默认返回 false 表示所有表都需要拼多租户条件 @Override public boolean ignoreTable(String tableName) { return !"user".equalsIgnoreCase(tableName); } })); // 如果用了分页插件注意先 add TenantLineInnerInterceptor 再 add PaginationInnerInterceptor // 用了分页插件必须设置 MybatisConfiguration#useDeprecatedExecutor = false // interceptor.addInnerInterceptor(new PaginationInnerInterceptor()); return interceptor; }  // @Bean // public ConfigurationCustomizer configurationCustomizer() { // return configuration -> configuration.setUseDeprecatedExecutor(false); // }}

"1.0" encoding="UTF-8"?>  create-tenant 
  DROP TABLE IF EXISTS `sys_user${tenant_id}`; CREATE TABLE `sys_user${tenant_id}` ( `user_id`         bigint(20) UNSIGNED                                           NOT NULL AUTO_INCREMENT COMMENT '用户编号', `account_user_id` bigint(20) UNSIGNED                                           NOT NULL COMMENT '账户用户编号', `nickname`        varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '昵称', `real_name`       varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名', `phone`           char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci     NOT NULL COMMENT '手机号', `avatar`          varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户头像', `email`           varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户邮箱', `gender`          tinyint(4) UNSIGNED                                           NOT NULL COMMENT '性别[enum]', `department_id`   bigint(20) UNSIGNED                                           NOT NULL COMMENT '部门编号', `enable`          tinyint(4) UNSIGNED                                           NOT NULL COMMENT '启用禁用[enum]', `last_login_time` datetime(0)                                                   NOT NULL COMMENT '最后登录时间', `last_login_ip`   varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '最后登录IP', `create_time`     datetime(0)                                                   NOT NULL COMMENT '创建时间', `update_time`     datetime(0)                                                   NOT NULL COMMENT '更新时间', `create_user_id`  bigint(20) UNSIGNED                                           NOT NULL COMMENT '创建人编号', `update_user_id`  bigint(20) UNSIGNED                                           NOT NULL COMMENT '更新人编号', `is_deleted`      tinyint(4) UNSIGNED                                           NOT NULL DEFAULT 0 COMMENT '逻辑删除[enum]', PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;  ......  
 

/** * @author 谷子毅 * @date 2022/4/2 */@Componentpublic class ExecSqlConfig {  private final Map sqlContainer = new HashMap<>();,>  private ExecSqlConfig() throws Exception { // 1.创建Reader对象 SAXReader reader = new SAXReader(); // 2.加载xml InputStream inputStream = new ClassPathResource("sql-script.xml").getInputStream(); Document document = reader.read(inputStream); // 3.获取根节点 Element root = document.getRootElement();  // 4.遍历每个statement List statements = root.elements("statement"); for (Element statement : statements) { String name = null; String sql = null; List elements = statement.elements(); // 5.拿到name和script加载到内存中管理 for (Element element : elements) { if ("name".equals(element.getName())) { name = element.getText(); } else if ("script".equals(element.getName())) { sql = element.getText(); } } sqlContainer.put(name, sql); } }  public String get(String name) { return sqlContainer.get(name); }}

/** * sql脚本执行工具 * @author 谷子毅 * @date 2022/4/2 */public class ExecSqlUtil {  private static final ExecSqlConfig EXEC_SQL_CONFIG = SpringContextHolder.getBean(ExecSqlConfig.class);  private static final DataSource DATA_SOURCE = SpringContextHolder.getBean(DataSource.class);  @SneakyThrows public static void execSql(String name, Map replaceMap),> { // 获取SQL脚本模板 String sql = EXEC_SQL_CONFIG.get(name);  // 替换模板变量 for (Map.Entry entity : replaceMap.entrySet()) {,> sql = sql.replace(entity.getKey(), entity.getValue()); } ScriptRunner scriptRunner = new ScriptRunner(DATA_SOURCE.getConnection());  // 执行SQL scriptRunner.runScript(new StringReader(sql)); } }

public void addTenant(TenantInsertDTO dto) {  // 在租户表中新增租户信息 ......   // 执行sql语句创建新租户的数据库表 ExecSqlUtil.execSql(SqlStatement.CREATE_TENANT, Collections.singletonMap("${tenant_id}", dto.getTenantId()));  // 向新建的数据表中插入租户的一些初始数据 ...... 

@Configuration@MapperScan("com.baomidou.mybatisplus.samples.dytablename.mapper")public class MybatisPlusConfig {  @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor(); dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> { // 获取参数方法 Map paramMap = RequestDataHelper.getRequestData();,> paramMap.forEach((k, v) -> System.err.println(k + "----" + v));  String year = "_2018"; int random = new Random().nextInt(10); if (random % 2 == 1) { year = "_2019"; } return tableName + year; }); interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor); // 3.4.3.2 作废该方式 // dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map); return interceptor; }}

dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> { if(IgnoreTables.contains(tableName)) { return tableName; } String tenantId = SecurityUtil.getTenantId(); return tableName + "_" + tenantId;});

"1.0" encoding="UTF-8"?>  create-tenant 
 CREATE DATABASE ${database};  USE ${database};  DROP TABLE IF EXISTS `sys_user`; CREATE TABLE `sys_user` ( `user_id`         bigint(20) UNSIGNED                                           NOT NULL AUTO_INCREMENT COMMENT '用户编号', `account_user_id` bigint(20) UNSIGNED                                           NOT NULL COMMENT '账户用户编号', `nickname`        varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '昵称', `real_name`       varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名', `phone`           char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci     NOT NULL COMMENT '手机号', `avatar`          varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户头像', `email`           varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户邮箱', `gender`          tinyint(4) UNSIGNED                                           NOT NULL COMMENT '性别[enum]', `department_id`   bigint(20) UNSIGNED                                           NOT NULL COMMENT '部门编号', `enable`          tinyint(4) UNSIGNED                                           NOT NULL COMMENT '启用禁用[enum]', `last_login_time` datetime(0)                                                   NOT NULL COMMENT '最后登录时间', `last_login_ip`   varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '最后登录IP', `create_time`     datetime(0)                                                   NOT NULL COMMENT '创建时间', `update_time`     datetime(0)                                                   NOT NULL COMMENT '更新时间', `create_user_id`  bigint(20) UNSIGNED                                           NOT NULL COMMENT '创建人编号', `update_user_id`  bigint(20) UNSIGNED                                           NOT NULL COMMENT '更新人编号', `is_deleted`      tinyint(4) UNSIGNED                                           NOT NULL DEFAULT 0 COMMENT '逻辑删除[enum]', PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;  ......  
 

dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> { if(IgnoreTables.contains(tableName)) { return tableName; } String tenantId = SecurityUtil.getTenantId(); return tenantId + "." + tableName;});