概述
在專案開發到一半時，使用者突然提出需要多個分支一起使用，這需要將系統設計成SaaS架構，將各個分支的資料隔離開來。
SaaS 實施
獨立資料庫：每個企業獨立物理資料庫，隔離性好，成本高。 共享資料庫和獨立架構是一台物理機和多個邏輯資料庫，分別稱為 oracle 的 schema 和 mysql 的資料庫，每個企業都有乙個獨立的 schema。 共享資料庫和資料庫表（本次使用）：在表中新增“企業”或“租戶”字段，以區分資料是哪個企業。 在操作過程中，您可以根據租戶字段查詢相應的資料。 優點：所有租戶使用相同的資料庫，因此成本較低。 缺點：隔離級別低，安全性低，開發過程中需要增加安全開發量，資料備份和恢復難度最大。 轉型思路
此次採用共享資料庫和資料庫表的SaaS方案。 在轉換過程中，需要執行以下操作： 建立租戶資訊表。 首先，將租戶 ID 字段新增到所有表中。 用於關聯租戶資訊表。 為租戶 ID 和原始表 ID 建立聯合主鍵。 注意主鍵的順序，原表的主鍵必須在左邊。 將該錶修改為分割槽表。 轉換後，在新增租戶資訊時，還可以將租戶的分割槽新增到所有表中，以儲存租戶的資料。 新增記錄時，需要使用租戶 ID 字段作為租戶 ID 欄位的值，並使用租戶 ID 作為條件，以便在刪除和修改查詢時操作 where 條件中的租戶資料。 測試環境介紹
測試庫中有 5 個表，下面我將使用 sys 日誌表進行測試。
為 sys log 建立表的語句如下：
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='系統日誌'查詢未新增“租戶 ID”欄位的表。
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') ;
建立租戶資訊表
僅供參考，以儲存租戶資訊。
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 '公司徽標位址', `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 '區域碼用於快速匹配後顯示區域ID，如廣州440100', primary key (`tenant_id`) using btree) engine=innodb default charset=utf8 comment='租戶基本資訊表';drop procedure if exists addcolumn ;delimiter ?建立過程 addcolumn （）begin --define table name 變數 declare s tablename varchar （100） ;* 資料庫中顯示該錶的所有表，從資訊模式中選擇表名tables where table_schema='databasename' order by table_name ;顯示從資訊架構中選擇表名的所有宣告 cur 表結構游標tables where table_schema = 'my'--my = 我的測試資料庫名稱和表名不在 （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 ; #using @c; fetch cur_table_structure into s_tablename ; end while ; close cur_table_structure ;end ?delimiter ;執行儲存過程呼叫 addcolumn（）。目標：在新增租戶時向所有表新增分割槽。
所需條件：
該錶必須是分割槽表，如果不是分割槽表，則需要將其更改為分割槽表。 租戶 ID 必須與原始表的日誌 ID 的主鍵組合。 將表修改為元件表
有三種方法可以向表新增分割槽：
建立乙個臨時分割槽表sys日誌副本，刪除舊的sys日誌，然後將sys日誌副本修改為sys日誌（這次詳見下文）直接將表修改為分割槽表，不需要原表中的資料，否則不會成功
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);alter table sys_log_copy add partition( partition a4 values in (4) engine = innodb, partition a5 values in (5) engine = innodb, partition a6 values in (6) engine = innodb);檢視建立表的語句
show create table `sys_log`;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);注意上面預設的 charset=utf8mb4 row format=dynamic驗證分割槽：Charset=UTF8MB4 是因為 UTF8 在 MySQL 中是不合理的編碼。
row format=dynamic 用於避免長度過大時出現以下錯誤
error 1709 (hy000): index column size too large. the maximum column size is 767 bytes.
也可在我的ini配置檔案設定為true可以解決這個問題，但是重啟資料庫會很麻煩。
mysqld]
innodb_large_prefix=true
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' ;您可以檢視已新增的三個分割槽
將資料複製到複製表
insert into `sys_log_copy` select * from `sys_log`儲存過程用於向分割槽表新增分割槽。
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 ;宣告為 v tablename varchar （200） 的表名，用於記錄游標中是否存在分割槽將分割槽新增到快取時 SQL 宣告 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 ;定義乙個游標，其值為所有分割槽表的表名宣告 curr 游標 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) ;如果沒有受影響的記錄，程式將繼續執行宣告繼續處理程式，以便找到未找到集 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 ;僅當 v loonum > 0 時才開啟游標，然後 --open curr ;迴圈讀取迴圈 ： 迴圈 -- 宣告迴圈的結束 if v num >= v loonum then le**e read loop ; end if ;將變數 fetch curr 的游標值取到 v tablename 中;如果沒有，則新增乙個分割槽集 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) ' ) set @v_sql = v_sql ;預處理需要執行動態 SQL 語句，其中 stmt 是乙個變數 prepare stmt from @v sql ;執行SQL語句 execute stmt;釋放預處理段解除分配準備stmt; end if ;結束迴圈讀迴圈;- 關閉 curr ; end if ;end?delimiter ;call add_table_partition (8) ;如果表未分割槽，則在呼叫儲存過程時會報告以下錯誤：錯誤**：無法對未分割槽表進行 1505partition 管理，即“無法對未分割槽表進行分割槽管理”。 可能會報告以下錯誤：錯誤**：1329no data - 提取、選擇或處理的行數為零，但如果查詢以下資訊架構分割槽正確，即分割槽新增成功。 這可以通過在定義游標之後和開啟游標之前新增以下內容來解決：declare continue handler for not found set is_found=0;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' ;我們可能需要這種場景需求。
乙個集團公司有多個子公司，每個集團和每個子公司本身就是乙個租戶，但子公司下也有子公司。 無論是集團公司還是其子公司，都有相應的使用者（t users）。 使用者需要有許可權才能在下面檢視自己公司的資料和子公司的資料。 從上面的場景需求中，我們知道 t 租戶表需要設計為樹樁結構。 讓我們來測試一下。
將上面的 T 租戶表修改為：
create table `t_tenant` (tenant_id` varchar(40) not null default '0' comment '租戶 ID', `path` varchar(200) default not null comment '從根節點開始的 ID 樹（例如 0-2-21-211-2111）通過"-"分開，盡頭是你自己的ID', `tenant_code` varchar(100) default null comment '租戶程式碼', `name` varchar(50) default null comment '租戶名稱', `logo` varchar(255) default null comment '公司徽標位址', `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 '街道門牌號位址', primary key (`tenant_id`) using btree) engine=innodb default charset=utf8 comment='租戶基本資訊表'修改地點是：T 租戶樹的路徑通過路徑進行快取。
建立使用者表（t 使用者）並新增測試使用者
測試的使用者 ID 和租戶 ID 必須對應。
建立附件表（t檔案）並新增測試業務資料
“建立者”欄位與“T 使用者”表相關聯，也與租戶 ID 相關聯，指示資料是哪個子公司。
參加考試
檢查租戶 ID 是什麼"211"的租戶資訊及其下的子租戶資訊。
select
tt.`tenant_id`,tt.path
fromt_tenant tt
where
select
instr(tt.path, "211"檢查租戶 ID 是什麼"211"以及 SELECT 下子租戶的附件資訊
fromt_file tf
where tf.`tenant_id` in
select
tt.`tenant_id`
fromt_tenant tt
where
select
instr(tt.path, "211")))
檢查租戶 ID 是什麼"2"以及其下子租戶的附件資訊你可以使用 mybatis*** 檢視子租戶的資料
寫***
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 j**a.sql.connection;import j**a.util.properties;import j**a.util.regex.matcher;import j**a.util.regex.pattern;* 實現擷取SELECT語句，實現尾部串聯SQL查詢租戶和子租戶的資訊 * 作者longxiaonan@aliyuncom */@slf4j@configuration@intercepts()}public class sqlinterceptor implements interceptor getsqlbyinvocation(metaobject, invocation); return invocation.proceed();拼接 SQL 執行 * 引數元物件 * 引數呼叫 * 返回 * 私有字串 getsqlbyinvocation（metaobject metaobject， invocation invocation） 丟擲 nosuchfieldException， illegalaccessexception * 連線原始 sql * 引數 sql * return * 靜態字串 adddatasql（string sql） sbappend(" where "); sb.append(suffsql); log.info("sql：--替換資料許可權後" + sb.tostring())return sb.tostring();override public object plugin(object target) override public void setproperties(properties properties) }/* *copyright (c) 2011-2020, baomidou ([email protected]). licensed under the apache license, version 2.0 (the "license"); you may not * use this file except in compliance with the license. you may obtain a copy of * the license at * unless required by applicable law or agreed to in writing, software * distributed under the license is distributed on an "as is" basis, without * warranties or conditions of any kind, either express or implied. see the * license for the specific language governing permissions and limitations under * the license. */package com.baomidou.mybatisplus.core.toolkit;import org.apache.ibatis.reflection.metaobject;import org.apache.ibatis.reflection.systemmetaobject;import j**a.lang.reflect.proxy;import j**a.util.properties;** 外掛程式實用程式類 * 作者 陶宇、湖濱 * 自 2017-06-20 起 * 公共最終類 Pluginutils ** 獲取真實的處理物件，可能是多層的**。 / @suppresswarnings("unchecked") public static t realtarget(object target) return (t) target;根據 key * public static string getproperty（properties properties， string key） } 獲取屬性的值在測試過程中發現，只要使用 select 語句，就會關聯查詢子租戶的資訊。 ·end·
作者：蕭嵐子**：覺進CN POST 6844903993085263886版權宣告：內容僅供學習研究之用，版權歸原作者所有。 如有任何侵權行為，請告知我們，我們將立即刪除並道歉。 謝謝！