DBA技巧：構建可重用MySQL查詢——視圖、內置函數、用戶函數與存儲過程

可重用查詢四大組件

1. 視圖：封裝複雜JOIN、隱藏敏感列、縮減列集提速
2. 內置函數：GROUP_CONCAT、COUNT/MONTH/MONTHNAME、CASE WHEN
3. 用戶自定義函數(UDF)：業務邏輯標準化
4. 存儲過程：參數化、模塊化、權限控制

視圖實戰：六表JOIN扁平化用戶畫像

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

調用：SELECT * FROM vusers WHERE user_id = 100; —— **一行代碼替換六次JOIN**。

內置函數妙用

• GROUP_CONCAT()：行轉列，生成交叉報表橫軸
• CASE WHEN：動態分桶、條件聚合
• 日期函數族：MONTH()/MONTHNAME()直接輸出報表標籤

自定義函數示例：參數非空校驗

CREATE FUNCTION is_empty(param_name VARCHAR(255)) RETURNS TINYINT
BEGIN
    DECLARE isEmpty TINYINT;
    SET isEmpty = (param_name IS NULL OR CHAR_LENGTH(TRIM(param_name)) = 0);
    RETURN isEmpty;
END;

調用：SELECT is_empty(@input_param); —— 統一入口、可測試、可復用。

存儲過程進化史：從硬編碼到通用報表

1. V1 硬編碼年份：WHERE YEAR(award_date)=2010 → 明年失效
2. V2 參數化年份：IN @year INT → WHERE YEAR(award_date)=@year
3. V3 雙日期范圍：IN @StartDate DATETIME, @EndDate DATETIME → WHERE award_date BETWEEN @StartDate AND @EndDate → 支持財政年、自然月任意范圍

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

原則

「視圖扁平化JOIN，函數標準化邏輯，存儲過程參數化業務，內置函數儘量用。」

局部性原理：近期用過的查詢大概率再用，**投資可重用性，收益複利增長**。