您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

打造可重用 MySQL 查詢：視圖、函數、存儲過程實戰模式

編輯：MySQL 資料庫相關知識

前言：重用不只是存儲過程

許多開發者將「可重用 SQL」等同於「存儲過程」，實則視圖、內建函數、使用者定義函數 (UDF) 同樣重要，甚至更輕量。本文示範如何組合四大元素，構建可維護、高效能、安全的查詢重用體系。

一、視圖：封裝複雜連接、簡化存取、提供安全

場景：用戶畫像需 6 表關聯

SELECT * 
FROM Users u
INNER JOIN UserPhoneNumbers upn ON u.user_id = upn.user_id
INNER JOIN UserScreenNames usn ON u.user_id = usn.user_id
INNER JOIN UserAffiliations ua ON u.user_id = ua.user_id
INNER JOIN Affiliations a ON a.affiliation_id = ua.affiliation_id
INNER JOIN UserWorkHistory uwh ON u.user_id = uwh.user_id
INNER JOIN Affiliations wa ON uwh.affiliation_id = wa.affiliation_id;

建立視圖封裝

CREATE VIEW `v_user_profile` AS
SELECT 
  u.user_id, u.email, u.created_at,
  upn.phone_number,
  usn.screen_name,
  a.name AS current_affiliation,
  wa.name AS work_affiliation,
  uwh.start_date, uwh.end_date, uwh.role
FROM Users u
INNER JOIN UserPhoneNumbers upn ON u.user_id = upn.user_id
INNER JOIN UserScreenNames usn ON u.user_id = usn.user_id
INNER JOIN UserAffiliations ua ON u.user_id = ua.user_id
INNER JOIN Affiliations a ON a.affiliation_id = ua.affiliation_id
INNER JOIN UserWorkHistory uwh ON u.user_id = uwh.user_id
INNER JOIN Affiliations wa ON uwh.affiliation_id = wa.affiliation_id;

應用層簡化調用

-- 原本 6 表 JOIN，現在單表查詢
SELECT * FROM v_user_profile WHERE user_id = 100;

-- 進一步：只選需要欄位（利用覆蓋索引潛力）
SELECT email, screen_name, current_affiliation FROM v_user_profile WHERE user_id = 100;

視圖進階用法

安全性：CREATE VIEW v_user_public AS SELECT user_id, screen_name FROM Users; 隱藏敏感欄位（密碼雜湊、Email、審計欄位）。
效能：視圖定義中只選擇索引欄位，避免 SELECT * 觸發回表（MySQL 視圖合併演算法 Merge 可下推 WHERE 條件）。
TEMPTABLE vs MERGE：含 GROUP BY/DISTINCT/UNION/子查詢的視圖強制 TEMPTABLE 算法（建立臨時表），效能較差；優先設計可 MERGE 的視圖。
可更新視圖：單表、無聚合、無 DISTINCT、主鍵包含 → 支援 INSERT/UPDATE/DELETE 直寫視圖。

二、內建函數：善用既有能力避免重造輪子

函數	用途	重用場景
`GROUP_CONCAT()`	行轉列、聚合字串	動態產生交叉表欄位、IN 列表構建
`JSON_ARRAYAGG()` / `JSON_OBJECTAGG()`	聚合為 JSON	API 直接回傳結構化資料，減少應用層組裝
`COALESCE()` / `IFNULL()`	空值處理	統一預設值邏輯，避免應用層判斷
`CASE WHEN ... THEN ... ELSE ... END`	條件邏輯	將業務規則下推資料庫（如等級計算、狀態映射）
`DATE_FORMAT()`, `MONTH()`, `QUARTER()`	時間維度切分	報表群組維度標準化

實例：動態交叉表欄位

SELECT 
  u.user_id,
  GROUP_CONCAT(DISTINCT CONCAT('month_', MONTH(o.created_at), '_total') ORDER BY MONTH(o.created_at)) AS months,
  GROUP_CONCAT(DISTINCT CONCAT('SUM(CASE WHEN MONTH(o.created_at)=', MONTH(o.created_at), ' THEN o.amount ELSE 0 END) AS month_', MONTH(o.created_at), '_total') ORDER BY MONTH(o.created_at)) AS select_cols
FROM Users u
LEFT JOIN Orders o ON u.user_id = o.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.user_id;

應用層取得 select_cols 動態構建最終查詢，實現「寫一次 SQL 模板，跑全年月度報表」。

三、使用者定義函數 (UDF)：封裝純邏輯、參數化重用

建立函數：檢查必填參數非空

DELIMITER //
CREATE FUNCTION `fn_is_empty`(p_param_name VARCHAR(255)) 
RETURNS TINYINT
DETERMINISTIC
NO SQL
BEGIN
  DECLARE v_empty TINYINT;
  SET v_empty = (p_param_name IS NULL OR CHAR_LENGTH(TRIM(p_param_name)) = 0);
  RETURN v_empty;
END//
DELIMITER ;

調用方式

-- 在存儲過程/觸發器/查詢中使用
SELECT fn_is_empty(''), fn_is_empty('  '), fn_is_empty('valid'), fn_is_empty(NULL);
-- 結果: 1, 1, 0, 1

-- 存儲過程參數驗證
IF fn_is_empty(p_email) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email 不可為空';
END IF;

UDF 開發原則

純邏輯、無副作用：不修改資料、不發送郵件、不呼叫外部 API。
DETERMINISTIC：相同輸入必然相同輸出，允許優化器快取結果。
NO SQL / READS SQL DATA：宣告存取類型，避免誤判。
優先搜尋現成庫：mysqludf.org、GitHub Awesome MySQL UDF，避免重複造輪子。
效能敏感改用 C/C++ UDF：複雜運算（地理空間、加密、壓縮）編寫原生插件，效能提升 10-100 倍。

四、存儲過程：業務流程編排、參數化、事務控制

演進歷程：從硬編碼到通用化

階段 1：固定年份報表（不可重用）

CREATE PROCEDURE `p_2010_bonuses_lst`()
BEGIN
  SELECT e.name, e.salary, COUNT(b.bonus_id) AS 'Total Bonuses'
  FROM employees e
  LEFT OUTER JOIN (
    SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = 2010
  ) b ON e.id = b.emp_id
  GROUP BY e.id;
END;

階段 2：參數化年份（年度重用）

CREATE PROCEDURE `p_yearly_bonuses_lst`(IN p_year INT)
BEGIN
  SELECT e.name, e.salary, COUNT(b.bonus_id) AS 'Total Bonuses'
  FROM employees e
  LEFT OUTER JOIN (
    SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = p_year
  ) b ON e.id = b.emp_id
  GROUP BY e.id;
END;

階段 3：日期範圍參數（財年、季度、任意區間）

CREATE PROCEDURE `p_bonuses_lst`(
  IN p_start_date DATETIME,
  IN p_end_date DATETIME
)
BEGIN
  SELECT e.name, e.salary, COUNT(b.bonus_id) AS 'Total Bonuses'
  FROM employees e
  LEFT OUTER JOIN (
    SELECT emp_id, bonus_id 
    FROM bonuses 
    WHERE award_date BETWEEN p_start_date AND p_end_date
  ) b ON e.id = b.emp_id
  GROUP BY e.id;
END;

存儲過程進階模式

模式	關鍵點	適用場景
動態 SQL (`PREPARE/EXECUTE`)	表名/欄位名參數化、條件動態組裝	通用分頁、多租戶 Schema、動態建表
游標循環處理	`DECLARE CURSOR`、`FETCH`、`CONTINUE HANDLER`	逐行複雜邏輯、批量發送通知、資料遷移腳本
事務控制	`START TRANSACTION`、`COMMIT/ROLLBACK`、`SAVEPOINT`	多步業務原子性（轉帳、下單扣庫存）
錯誤處理	`DECLARE ... HANDLER FOR SQLEXCEPTION`、`SIGNAL/RESIGNAL`	統一錯誤碼、回滾並記錄審計日誌
權限隔離	`SQL SECURITY DEFINER/INVOKER`	應用用戶無直連表權限，僅能呼叫過程

動態分頁範例 (PREPARE 模式)

CREATE PROCEDURE `p_paginate`(
  IN p_table VARCHAR(64),
  IN p_where VARCHAR(500),
  IN p_order VARCHAR(100),
  IN p_page INT,
  IN p_size INT
)
BEGIN
  DECLARE v_offset INT DEFAULT (p_page - 1) * p_size;
  SET @sql = CONCAT('SELECT * FROM `', p_table, '` WHERE ', p_where, ' ORDER BY ', p_order, ' LIMIT ?, ?');
  PREPARE stmt FROM @sql;
  EXECUTE stmt USING v_offset, p_size;
  DEALLOCATE PREPARE stmt;
END;

⚠ 安全警示：動態 SQL 極易注入，**嚴禁直接拼接使用者輸入**，必須白名單驗證 p_table、p_order、p_where，或改用應用層構建。

五、四大元素協同架構圖

┌─────────────────────────────────────┐
│          應用層 / API 層             │
│  呼叫存儲過程 / 查詢視圖 / 使用函數  │
└──────────────┬──────────────────────┘
               │
┌──────────────▼──────────────────────┐
│           存儲過程層                 │
│  業務流程編排、事務控制、動態 SQL    │
│  呼叫 UDF、查詢視圖                  │
└──────────────┬──────────────────────┘
               │
┌──────────────▼──────────────────────┐
│           視圖層                     │
│  複雜 JOIN 封裝、欄位權限控制、      │
│  衍生欄位標準化 (如全名、年齡)        │
└──────────────┬──────────────────────┘
               │
┌──────────────▼──────────────────────┐
│        函數層 (內建 + UDF)           │
│  純邏輯封裝：驗證、計算、格式化、     │
│  聚合轉換、條件映射                  │
└──────────────┬──────────────────────┘
               │
┌──────────────▼──────────────────────┐
│           基礎表層                   │
│  正規化 Schema、索引、約束、分區      │
└─────────────────────────────────────┘

六、最佳實踐總結

視圖優先：封裝固定 JOIN 模式、權限控制、衍生欄位；避免在視圖疊加視圖（嵌套深度 > 2 效能風險）。
函數次之：封裝無副作用純邏輯（驗證、計算、格式化）；DETERMINISTIC 讓優化器快取。
存儲過程最後：編排多步業務、事務控制、動態 SQL、權限邊界；單一查詢無需過程。
參數化一切：硬編碼值 → 參數 → 配置表 → 應用層配置，層層解耦。
命名規範：v_ 視圖、fn_ 函數、p_ 存儲過程、trg_ 觸發器，一眼識別類型。
版本控管：所有物件 DDL 納入 Git (Flyway/Liquibase)，避免「線上改過沒遷移腳本」。
效能基線：新增視圖/函數/過程前跑 EXPLAIN、壓測，防止引入全表掃描、臨時表、鎖升級。

結語

可重用查詢體系的核心是**分層封裝與職責單一**：視圖管結構、函數管邏輯、過程管流程、表管資料。遵循「視圖 > 函數 > 過程」的輕量優先原則，配合參數化、版本控管、效能基線，才能讓 SQL 資產隨業務增長而演進，而非腐化成不可維護的「義大利麵條代碼」。工欲善其事，必先利其器：SHOW CREATE VIEW/FUNCTION/PROCEDURE、information_schema.ROUTINES、pt-show-grants 是治理利器。