MySQL 進階核心技術全解析：視圖、存儲過程、函數、事務、觸發器與動態 SQL

前言

接上篇基礎操作，本文進入 MySQL 進階核心特性：視圖、存儲過程、自定義函數、事務控制、觸發器、動態 SQL 執行。並附 Python (PyMySQL) 調用示例，幫助開發者從理論落地到工程實踐。

一、視圖

概念

視圖是虛擬表，本質是預定義的 SELECT 語句。不存儲數據，查詢時動態執行。主要用途：

• 安全性：隱藏敏感列/行 (如薪資、密碼哈希)
• 簡化複雜查詢：將多表連接、聚合封裝為單一視圖
• 兼容性：表結構變更時，調整視圖定義即可，應用層 SQL 無需改動

核心操作

二、存儲過程

為什麼需要存儲過程？

• 減少網絡傳輸：多條 SQL 打包一次調用
• 防 SQL 注入：參數化執行，邏輯在服務端
• 統一業務邏輯：多語言應用 (Web/桌面/移動) 共享同一套數據邏輯，維護成本低
• 權限控制：只授予 EXECUTE 權限，不給表直接操作權

創建語法與參數模式

DELIMITER //
CREATE PROCEDURE proc_name (
    IN  param1 INT,      -- 僅輸入
    OUT param2 INT,      -- 僅輸出
    INOUT param3 INT     -- 輸入輸出雙向
)
BEGIN
    DECLARE temp_var INT DEFAULT 0;
    -- 邏輯代碼
    SET param2 = param1 + param3;
    SET param3 = param3 + 100;
END//
DELIMITER ;

調用與獲取返回值

-- MySQL 客戶端
SET @t1 = 3;
SET @t2 = 0;
CALL proc_name(1, 2, @t1, @t2);
SELECT @t1, @t2;  -- 查看 OUT/INOUT 返回值

Python (PyMySQL) 調用示例

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql

conn = pymysql.connect(
    host='127.0.0.1', port=3306,
    user='root', passwd='', db='test_db',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with conn.cursor() as cursor:
        # 調用存儲過程
        cursor.callproc('proc_name', (1, 2, 3))  # 傳入參數
        # 獲取結果集 (若存儲過程有 SELECT)
        result_set = cursor.fetchall()
        print('Result set:', result_set)
        # 獲取 OUT/INOUT 參數
        cursor.execute('SELECT @_proc_name_0, @_proc_name_1, @_proc_name_2')
        out_params = cursor.fetchone()
        print('Out params:', out_params)
    conn.commit()
finally:
    conn.close()

關鍵點： PyMySQL 中 OUT/INOUT 參數需通過 SELECT @_proc_name_N 獲取，且必須 conn.commit() 才能持久化修改。

三、自定義函數

函數 vs 存儲過程

創建與使用

DELIMITER //
CREATE FUNCTION f_add(i1 INT, i2 INT) RETURNS INT
BEGIN
    DECLARE sum INT;
    SET sum = i1 + i2;
    RETURN sum;
END//
DELIMITER ;

-- 刪除
DROP FUNCTION f_add;

-- 在查詢中使用
SELECT f_add(11, nid), name FROM tb2;

-- 賦值給變量
SET @res = f_add(5, 6);
SELECT @res;

四、事務控制

ACID 原則與典型場景

轉賬業務：A 扣款、B 入賬，必須同成同敗。若扣款成功、入賬前網絡中斷，事務回滾保證數據一致性。

存儲過程中實現事務模板

DELIMITER //
CREATE PROCEDURE sp_transfer(OUT p_code TINYINT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_code = 1;  -- 錯誤
        ROLLBACK;
    END;
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        SET p_code = 2;  -- 警告
        ROLLBACK;
    END;

    START TRANSACTION;
    DELETE FROM tb1;              -- 業務 SQL 1
    INSERT INTO tb2(name) VALUES('seven'); -- 業務 SQL 2
    COMMIT;
    SET p_code = 0;  -- 成功
END//
DELIMITER ;

-- 調用
SET @ret = 0;
CALL sp_transfer(@ret);
SELECT @ret;  -- 0 成功，1 異常回滾，2 警告回滾

五、觸發器

六大觸發時機

語法模板

DELIMITER //
CREATE TRIGGER trg_name
BEFORE INSERT ON tb1
FOR EACH ROW
BEGIN
    -- NEW 代表新插入行，OLD 代表舊行 (UPDATE/DELETE 可用)
    IF NEW.name = 'aylin' THEN
        INSERT INTO tb2(name) VALUES ('張巖林');
    END IF;
END//
DELIMITER ;

實戰示例：插入後根據條件記錄日志

DELIMITER //
CREATE TRIGGER trg_after_ins_tb1
AFTER INSERT ON tb1
FOR EACH ROW
BEGIN
    IF NEW.num = 666 THEN
        INSERT INTO tb2(name) VALUES ('張巖林'), ('很帥');
    ELSEIF NEW.num = 555 THEN
        INSERT INTO tb2(name) VALUES ('aylin'), ('非常帥');
    END IF;
END//
DELIMITER ;

管理命令

-- 刪除
DROP TRIGGER trg_after_ins_tb1;

-- 觸發器無法手動調用，只能通過對表的 DML 操作被動觸發
INSERT INTO tb1(name) VALUES ('張巖林');  -- 自動觸發上述 BEFORE INSERT 觸發器

六、動態 SQL 執行 (PREPARE/EXECUTE)

存儲過程中構建動態 SQL 字符串並執行：

DELIMITER //
CREATE PROCEDURE dynamic_query(IN tbl_name VARCHAR(64))
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', tbl_name, ' WHERE id > 10');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

應用場景：表名/列名作為參數、分表查詢、靈活構建複雜條件。

七、常用內置函數速查 (字符串/數學/日期)

完整函數列表請參考：MySQL 官方中文手冊

總結

• 視圖：封裝查詢、安全隔離、不可直接寫入。
• 存儲過程：邏輯下沉、防注入、多語言共享、支持事務與動態 SQL。
• 函數：可在 SQL 表達式中直接調用，但無事務/動態 SQL 能力。
• 事務：START TRANSACTION + 錯誤處理器 + COMMIT/ROLLBACK 是標準模式。
• 觸發器：BEFORE/AFTER + INSERT/UPDATE/DELETE，利用 NEW/OLD 訪問行數據，慎用避免隱式邏輯導致除錯困難。
• 動態 SQL：PREPARE/EXECUTE/DEALLOCATE 三部曲，防注入需嚴格驗證拼接參數。

下一章將深入講解索引原理與優化實戰，敬請期待！