Implementing SaaS Multi‑Tenant Architecture with Shared Database Table Partitioning in MySQL

When a project needs to serve multiple subsidiaries, the article proposes a SaaS solution based on a shared database with a tenant column to isolate data.

It describes three architectural options—independent databases, shared database with separate schemas, and the chosen shared‑table approach—highlighting its low cost and the need for careful security handling.

The implementation steps include creating a tenant information table, adding a tenant_id column to every existing table, defining a composite primary key, and converting tables to partitioned tables using LIST partitioning on tenant_id . Example DDL for sys_log and the partition‑creation ALTER statements are provided:

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='系统日志';

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
);

SQL scripts are given to locate tables missing tenant_id , a stored procedure addColumn that adds the column to all tables, and another procedure add_table_partition that automatically creates a new partition for a given tenant:

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'
    );
  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;
    DEALLOCATE PREPARE msql;
    FETCH cur_table_structure INTO s_tablename;
  END WHILE;
  CLOSE cur_table_structure;
END ?
DELIMITER ;

DELIMITER ?
CREATE 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 cur 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 cur;
    read_loop: LOOP
      IF V_NUM >= V_LOONUM THEN LEAVE read_loop; END IF;
      FETCH cur 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 cur;
  END IF;
END ?
DELIMITER ;

For application‑level enforcement, a MyBatis interceptor SqlInterceptor is shown, which rewrites SELECT statements to append a tenant‑filter sub‑query, ensuring users can only access their own and descendant tenant data.

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;

/**
 * Intercepts SELECT statements to add tenant data permission logic.
 */
@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();
        }
        getSqlByInvocation(metaObject, invocation);
        return invocation.proceed();
    }

    private String getSqlByInvocation(MetaObject metaObject, Invocation invocation) throws NoSuchFieldException, IllegalAccessException {
        String originalSql = (String) metaObject.getValue(PluginUtils.DELEGATE_BOUNDSQL_SQL);
        metaObject.setValue(PluginUtils.DELEGATE_BOUNDSQL_SQL, originalSql);
        String targetSql = addDataSql(originalSql);
        return targetSql;
    }

    static String addDataSql(String sql) {
        sql = StringUtils.replace(sql, ";", "");
        StringBuilder sb = new StringBuilder(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);
        Matcher matcher = compile.matcher(sql);
        if (matcher.find()) {
            String whereLastSql = matcher.group(matcher.groupCount());
            int left = StringUtils.countMatches(whereLastSql, "(");
            int right = StringUtils.countMatches(whereLastSql, ")");
            if (left == right) {
                sb.append(" and ");
                sb.append(suffSql);
                return sb.toString();
            }
        }
        sb.append(" where ");
        sb.append(suffSql);
        return sb.toString();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, new SqlInterceptor());
    }

    @Override
    public void setProperties(Properties properties) {}
}

Finally, the article demonstrates testing the setup with sample data, queries that retrieve tenant hierarchies, and screenshots of the results, confirming that the partitioning and interceptor correctly enforce data isolation across tenants.