萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

PHP 實現 MySQL 動態視圖:基於 Session 變量的多租戶數據隔離

核心思路

利用 MySQL 會話變量(@session_var)配合自定義函數,在視圖定義中動態注入租戶 ID,實現「同一視圖、不同會話、看到不同數據」的多租戶隔離效果。

完整實現代碼

-- 1. 基礎表
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
    `id` INT NOT NULL,
    `name` VARCHAR(20) NOT NULL
);
INSERT INTO `test` VALUES 
(1,'test1'), (1,'test11'), (1,'test111'),
(2,'test2'), (2,'test22');

-- 2. 會話變量讀取函數
DROP FUNCTION IF EXISTS `getSpid`;
DELIMITER //
CREATE FUNCTION `getSpid()` RETURNS INT
DETERMINISTIC
RETURN @spid;
//
DELIMITER ;

-- 3. 動態視圖
DROP VIEW IF EXISTS `v_test`;
CREATE VIEW `v_test` AS
SELECT * FROM `test` WHERE `id` = getSpid();

測試驗證

會話 1(租戶 A)

SET @spid = 1;
SELECT * FROM v_test;
-- 結果:test1, test11, test111

會話 2(租戶 B)

SET @spid = 2;
SELECT * FROM v_test;
-- 結果:test2, test22

關鍵機制解析

組件作用生命周期
@spid 會話變量存儲當前租戶 ID會話級,連接斷開自動銷毀
getSpid() 函數封裝變量讀取,供視圖調用全局持久,需 DETERMINISTIC 聲明
v_test 視圖靜態定義動態過濾元數據持久,運行時動態求值

優缺點權衡

優點

  • 零侵入:業務代碼無需修改 SQL,只需設置 @spid
  • 性能佳:視圖解析時直接利用索引(id 列)
  • 安全性:會話隔離,無 SQL 注入風險

局限

  • 不支持預處理語句缓存(每次解析視圖)
  • 連接池復用時必須顯式重置 @spid
  • 無法用於 INSERT/UPDATE 視圖(需加 WITH CHECK OPTION 且函數不可變)

生產環境建議

  1. 在連接池獲取連接後、執行業務前,統一調用 SET @spid = ?
  2. 為 test.id 添加索引:ALTER TABLE test ADD INDEX idx_id (id);
  3. 監控 SHOW PROCESSLIST 確認變量正確傳遞
  4. 考慮使用 ROW_LEVEL_SECURITY(MySQL 8.0+)替代此方案

擴展:多租戶字段自動填充

CREATE TRIGGER `test_bi` BEFORE INSERT ON `test`
FOR EACH ROW SET NEW.`tenant_id` = getSpid();

配合視圖,可構建完整的「寫入自動標識、讀取自動過濾」閉環。

copyright © 萬盛學電腦網 all rights reserved