How to Build a Multi‑Tenant SaaS Architecture with MySQL Partitioning and MyBatis Interceptor

Scenario

During development a client required multiple subsidiaries to share the same system, which forced a refactor to a SaaS architecture with strict data isolation between tenants.

SaaS implementation options

Independent databases : each tenant gets its own physical database. Isolation is excellent but cost is high.

Shared database with independent schema : one physical server hosts multiple logical schemas (Oracle calls them schemas, MySQL calls them databases). Isolation improves, cost drops.

Shared database with tenant column (chosen) : a single table contains a tenant_id column to distinguish tenant data. Cost is lowest, but isolation and security require careful development.

Transformation steps for the shared‑database approach

Create a tenant information table ( t_tenant) to store basic tenant data.

Add a tenant_id column to every existing table.

Make (tenant_id, original_primary_key) a composite primary key, ensuring the original primary key stays on the left.

Convert each table to a partitioned table so that data for each tenant resides in its own partition.

Creating the tenant information table

CREATE TABLE `t_tenant` (
  `tenant_id` varchar(40) NOT NULL DEFAULT 'c12dee54f652452b88142a0267ec74b7' COMMENT '租户id',
  `tenant_code` varchar(100) DEFAULT NULL COMMENT '租户编码',
  `name` varchar(50) DEFAULT NULL COMMENT '租户名称',
  `desc` varchar(500) DEFAULT NULL COMMENT '租户描述',
  `logo` varchar(255) DEFAULT NULL COMMENT '公司logo地址',
  `status` smallint(6) DEFAULT NULL COMMENT '状态1有效0无效',
  `create_by` varchar(100) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `last_update_by` varchar(100) DEFAULT NULL COMMENT '最后修改人',
  `last_update_time` datetime DEFAULT NULL COMMENT '最后修改时间',
  `street_address` varchar(200) DEFAULT NULL COMMENT '街道楼号地址',
  `province` varchar(20) DEFAULT NULL COMMENT '一级行政单位,如广东省,上海市等',
  `city` varchar(20) DEFAULT NULL COMMENT '城市, 如广州市,佛山市等',
  `district` varchar(20) DEFAULT NULL COMMENT '行政区,如番禺区,天河区等',
  `link_man` varchar(50) DEFAULT NULL COMMENT '联系人',
  `link_phone` varchar(50) DEFAULT NULL COMMENT '联系电话',
  `longitude` decimal(10,6) DEFAULT NULL COMMENT '经度',
  `latitude` decimal(10,6) DEFAULT NULL COMMENT '纬度',
  `adcode` varchar(8) DEFAULT NULL COMMENT '区域编码',
  PRIMARY KEY (`tenant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='租户的基本信息表';

Adding tenant_id to all tables

First, create a stored procedure that scans information_schema.tables and adds the column to every table that does not already have it.

DROP PROCEDURE IF EXISTS addColumn ;
DELIMITER ?
CREATE PROCEDURE addColumn ()
BEGIN
  DECLARE s_tablename VARCHAR(100);
  DECLARE cur_table_structure CURSOR FOR
    SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'my'
    AND table_name NOT IN (
      SELECT t.table_name FROM (
        SELECT table_name, column_name FROM information_schema.columns WHERE table_name IN (
          SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'my'
        )
      ) t WHERE t.column_name = 'tenant_id'
    );
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename = NULL;
  OPEN cur_table_structure;
  FETCH cur_table_structure INTO s_tablename;
  WHILE (s_tablename IS NOT NULL) DO
    SET @MyQuery = CONCAT('ALTER TABLE `', s_tablename, '` ADD COLUMN `tenant_id` INT NOT NULL COMMENT ''租户id''');
    PREPARE msql FROM @MyQuery;
    EXECUTE msql;
    FETCH cur_table_structure INTO s_tablename;
  END WHILE;
  CLOSE cur_table_structure;
END ?
DELIMITER ;
CALL addColumn();

Modifying primary keys

After the column is added, alter each table so that (tenant_id, original_id) becomes the composite primary key, keeping the original primary key on the left side.

Converting tables to partitioned tables

Three ways exist to add partitions; the article chooses the "create a temporary copy table, copy data, drop the old table, rename the copy" method.

CREATE TABLE `sys_log_copy` (
  `log_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  `type` TINYINT(1) DEFAULT NULL COMMENT '类型',
  `content` VARCHAR(255) DEFAULT NULL COMMENT '内容',
  `create_id` BIGINT(18) DEFAULT NULL COMMENT '创建人ID',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `tenant_id` INT NOT NULL,
  PRIMARY KEY (`log_id`,`tenant_id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='系统日志'
PARTITION BY LIST COLUMNS (tenant_id) (
    PARTITION a1 VALUES IN (1) ENGINE=INNODB,
    PARTITION a2 VALUES IN (2) ENGINE=INNODB,
    PARTITION a3 VALUES IN (3) ENGINE=INNODB
);

Copy data into the new table:

INSERT INTO `sys_log_copy` SELECT * FROM `sys_log`;

Drop the original table and rename the copy to sys_log.

Verifying partitions

SELECT partition_name part, partition_expression expr, partition_description descr, table_rows
FROM information_schema.partitions
WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = 'sys_log_copy';

The query returns the three partitions (a1, a2, a3) defined above.

Automatic partition creation stored procedure

The following procedure adds a new partition for a given tenant id to every partitioned table.

DELIMITER ?
USE `my`?
DROP PROCEDURE IF EXISTS `add_table_partition`?
CREATE DEFINER=`root`@`%` PROCEDURE `add_table_partition`(IN _tenantId INT)
BEGIN
  DECLARE IS_FOUND INT DEFAULT 1;
  DECLARE v_tablename VARCHAR(200);
  DECLARE v_sql VARCHAR(5000);
  DECLARE V_P_VALUE VARCHAR(100) DEFAULT CONCAT('P', REPLACE(_tenantId, '-', ''));
  DECLARE V_COUNT INT;
  DECLARE V_LOONUM INT DEFAULT 0;
  DECLARE V_NUM INT DEFAULT 0;
  DECLARE curr CURSOR FOR
    SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.partitions t
    WHERE TABLE_SCHEMA = SCHEMA() AND t.partition_name IS NOT NULL GROUP BY t.TABLE_NAME;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET IS_FOUND=0;
  SELECT COUNT(1) INTO V_LOONUM FROM (
    SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.partitions t
    WHERE TABLE_SCHEMA = SCHEMA() AND t.partition_name IS NOT NULL GROUP BY t.TABLE_NAME) A;
  IF V_LOONUM > 0 THEN
    OPEN curr;
    read_loop: LOOP
      IF V_NUM >= V_LOONUM THEN LEAVE read_loop; END IF;
      FETCH curr INTO v_tablename;
      SET V_NUM = V_NUM + 1;
      SELECT COUNT(1) INTO V_COUNT FROM INFORMATION_SCHEMA.partitions t
        WHERE LOWER(t.TABLE_NAME) = LOWER(v_tablename) AND t.PARTITION_NAME = V_P_VALUE AND t.TABLE_SCHEMA = SCHEMA();
      IF V_COUNT <= 0 THEN
        SET v_sql = CONCAT('ALTER TABLE ', v_tablename, ' ADD PARTITION (PARTITION ', V_P_VALUE, ' VALUES IN(', _tenantId, ') ENGINE = INNODB)');
        PREPARE stmt FROM v_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
      END IF;
    END LOOP read_loop;
    CLOSE curr;
  END IF;
END?
DELIMITER ;
CALL add_table_partition(8);

If a table is not already partitioned, the call fails with error 1505: "Partition management on a not partitioned table is not possible".

Data‑permission enforcement with MyBatis interceptor

The interceptor rewrites every SELECT statement to add a condition that limits rows to the current tenant and its descendant tenants (stored in t_tenant.path).

package com.iee.orm.mybatis.common;

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.iee.orm.mybatis.common.UserHelper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.StatementType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.context.annotation.Configuration;
import java.sql.Connection;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
@Configuration
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class SqlInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        if (SqlCommandType.SELECT != mappedStatement.getSqlCommandType() || StatementType.CALLABLE == mappedStatement.getStatementType()) {
            return invocation.proceed();
        }
        String originalSql = (String) metaObject.getValue(PluginUtils.DELEGATE_BOUNDSQL_SQL);
        String tenantId = UserHelper.getTenantId();
        String suffSql = " `tenant_id` IN (SELECT tt.`tenant_id` FROM t_tenant tt WHERE INSTR(tt.path, " + tenantId + "))";
        String regex = "(.*)(where)(.*)";
        Pattern compile = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = compile.matcher(originalSql);
        StringBuilder sb = new StringBuilder(originalSql.replace(";", ""));
        if (matcher.find()) {
            String whereClause = matcher.group(3);
            int left = StringUtils.countMatches(whereClause, "(");
            int right = StringUtils.countMatches(whereClause, ")");
            if (left == right) {
                sb.append(" and ").append(suffSql);
                log.info("Data permission SQL: {}", sb.toString());
                metaObject.setValue(PluginUtils.DELEGATE_BOUNDSQL_SQL, sb.toString());
                return invocation.proceed();
            }
        }
        sb.append(" where ").append(suffSql);
        log.info("Data permission SQL: {}", sb.toString());
        metaObject.setValue(PluginUtils.DELEGATE_BOUNDSQL_SQL, sb.toString());
        return invocation.proceed();
    }
    @Override
    public Object plugin(Object target) { return Plugin.wrap(target, this); }
    @Override
    public void setProperties(Properties properties) { }
}

The interceptor obtains the current tenant via UserHelper.getTenantId() and appends a sub‑query that selects all tenant ids whose path contains the current tenant id, thereby granting access to descendant tenants.

Testing the data‑permission logic

Example queries:

SELECT tt.`tenant_id`, tt.path FROM t_tenant tt WHERE INSTR(tt.path, "211");

Retrieves tenant 211 and all its sub‑tenants.

SELECT * FROM t_file tf WHERE tf.`tenant_id` IN (
  SELECT tt.`tenant_id` FROM t_tenant tt WHERE INSTR(tt.path, "211")
);

Shows files belonging to tenant 211 and its children.

Important MySQL notes

MySQL utf8 is not a full Unicode charset; utf8mb4 avoids character‑size errors.

When a table’s row size exceeds 767 bytes, ROW_FORMAT=DYNAMIC prevents the "Index column size too large" error.

Alternatively, setting innodb_large_prefix=true in my.cnf solves the issue but requires a server restart.

All code and SQL statements are available in the accompanying GitHub repository:

https://github.com/longxiaonan/java-sea/tree/master/javasea-orm/javasea-orm-mybatis-Interceptor