MySQL 實現動態視圖：透過 Session 變數與自訂函數實現多租戶資料隔離

需求場景

C/S 架構應用，不同角色用戶查詢同一視圖但見到不同資料。要求：不修改視圖定義、不在外層包裝查詢。

設計思路

1. 系統產生唯一識別 ID（應用端或 UUID()）
2. 將 ID 存入 Session 變數 @spid
3. 建立自訂函數 getSpid() 回傳該變數
4. 視圖 WHERE 條件呼叫 getSpid() 過濾資料

完整實作碼

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. 建立取得 Session 變數的函數

DROP FUNCTION IF EXISTS getSpid;
DELIMITER |
CREATE FUNCTION getSpid()
RETURNS INT
RETURN @spid;
|
DELIMITER ;

3. 建立動態視圖

DROP VIEW IF EXISTS v_test;
CREATE VIEW v_test AS
SELECT * FROM test WHERE id = getSpid();

4. 測試驗證

-- Session 1：用戶 A
SET @spid = 1;
SELECT * FROM v_test;
-- 結果：test1, test11, test111

-- Session 2：用戶 B
SET @spid = 2;
SELECT * FROM v_test;
-- 結果：test2, test22

原理解析

應用擴展

• 多租戶 SaaS：@tenant_id 隔離客戶資料
• 資料權限控制：@role_id 配合權限表過濾
• 軟刪除過濾：@show_deleted 控制是否顯示已刪除資料

注意事項