Implementing Multi‑Tenant SaaS Architecture with MySQL Partitioning and MyBatis Interceptor

The article outlines a practical approach to transform an existing project into a SaaS system that serves multiple subsidiaries by isolating each company's data using a shared‑database, table‑level tenant_id column, and MySQL partitioning.

SaaS Implementation Options

Independent databases : each tenant has its own physical database – high isolation but costly.

Shared database with independent schemas : one MySQL instance, multiple schemas (Oracle calls them schemas, MySQL calls them databases).

Shared database with tenant_id column (chosen solution) : add a tenant_id column to every table and use it to filter data. This is low‑cost but requires careful security handling.

Refactoring Steps

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

Add a tenant_id column to all existing tables. ALTER TABLE `sys_log` ADD COLUMN `tenant_id` INT NOT NULL COMMENT '租户id';

Make tenant_id part of the primary key together with the original primary key. PRIMARY KEY (`log_id`,`tenant_id`) USING BTREE

Convert tables to partitioned tables (list partition by tenant_id ) so that each tenant's data resides in its own partition. ALTER TABLE sys_log 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 );

Testing Environment

The sys_log table is used for demonstration. Its creation script:

CREATE TABLE `sys_log` (
  `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=utf8 ROW_FORMAT=DYNAMIC COMMENT='系统日志';

Automating Partition Management

A stored procedure add_table_partition is provided to add a new partition for a given tenant ID to every partitioned table.

CREATE DEFINER=`root`@`%` PROCEDURE `add_table_partition`(IN _tenantId INT)
BEGIN
  DECLARE v_tablename VARCHAR(200);
  DECLARE v_sql VARCHAR(5000);
  DECLARE V_P_VALUE VARCHAR(100) DEFAULT CONCAT('P', REPLACE(_tenantId, '-', ''));
  DECLARE cur CURSOR FOR
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = SCHEMA() AND PARTITION_NAME IS NOT NULL GROUP BY TABLE_NAME;
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_tablename;
    IF v_tablename IS NULL THEN LEAVE read_loop; END IF;
    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 LOOP;
  CLOSE cur;
END;

Calling CALL add_table_partition(8); adds partition P8 to all partitioned tables.

MyBatis Interceptor for Data Permission

An interceptor modifies every SELECT statement to automatically filter rows belonging to the current tenant and its sub‑tenants.

package com.iee.orm.mybatis.common;

import org.apache.ibatis.plugin.*;
import java.sql.Connection;
import java.util.Properties;
import java.util.regex.*;

@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 {
        // obtain original SQL
        // append tenant filter using UserHelper.getTenantId()
        return invocation.proceed();
    }
    // helper method builds the tenant filter clause
    static String addDataSql(String sql) { /* ... */ }
    // other required methods omitted for brevity
}

The interceptor appends a clause like:

AND `tenant_id` IN (SELECT tt.`tenant_id` FROM t_tenant tt WHERE INSTR(tt.path,
))

Hierarchical Tenant Structure

The tenant table is extended with a path column to represent a tree structure (e.g., 0-2-21-211-2111 ). Queries can then retrieve a tenant and all its descendants using INSTR(tt.path, '211') .

Full Workflow

Design t_tenant with tenant_id and path .

Create user and file tables that reference tenant_id .

Convert existing tables to partitioned tables and add tenant_id columns.

Use the stored procedure to add partitions for new tenants.

Enable the MyBatis interceptor to enforce data permission automatically.

Finally, the article includes promotional material for a community, interview resources, and other unrelated links, but the technical core provides a complete guide for building a multi‑tenant SaaS system on MySQL.