MySQL 進階功能實戰筆記：存儲過程、觸發器、視圖與權限管理

常用管理指令

-- 查看伺服器實際運行參數
SHOW VARIABLES;

-- 查看使用者權限
SHOW GRANTS FOR 'root'@'localhost';

存儲過程

基本語法

-- 刪除已存在
DROP PROCEDURE IF EXISTS helloword;

-- 變更分隔符，避免 ; 衝突
DELIMITER //

CREATE PROCEDURE helloword()
BEGIN
    SELECT 'hello world';
END //

DELIMITER ;

-- 呼叫
CALL helloword();

帶參數與邏輯判斷

DROP PROCEDURE IF EXISTS pr_add;
DELIMITER //
CREATE PROCEDURE pr_add(IN a INT, IN b INT)
BEGIN
    IF a IS NULL THEN
        SET a = 0;
    END IF;
    SELECT a + b AS result;
END //
DELIMITER ;

CALL pr_add(NULL, 1);  -- 結果: 1

視圖

-- 建立視圖
CREATE VIEW v_user_active AS 
SELECT id, username, email FROM users WHERE status = 1;

-- 使用視圖
SELECT * FROM v_user_active;

觸發器

語法結構

DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- 觸發邏輯
END //
DELIMITER ;

實戰範例：審計日誌

DELIMITER //
CREATE TRIGGER trg_user_audit_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, created_at)
    VALUES ('users', 'INSERT', NEW.id, NOW());
END //
DELIMITER ;

NEW 代表新資料行，OLD 代表舊資料行（UPDATE/DELETE 可用）。

使用者與權限管理

權限層級

常用操作

-- 建立使用者並授權（僅限本地連線）
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost';

-- 允許遠端連線（生產環境請限制 IP）
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'192.168.1.%';

-- 撤銷特定權限
REVOKE INSERT ON mydb.* FROM 'app_user'@'localhost';

-- 修改密碼 (MySQL 5.7+)
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';

-- 刷新權限生效
FLUSH PRIVILEGES;

-- 刪除使用者
DROP USER 'app_user'@'localhost';

修改 root 密碼（需重啟略過授權表）

1. 停止 MySQL：systemctl stop mysqld
2. 以 --skip-grant-tables 啟動：mysqld_safe --skip-grant-tables &
3. 無密碼登入：mysql -u root
4. 執行：

   FLUSH PRIVILEGES;
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

5. 正常重啟 MySQL

最佳實踐提醒

• 避免在存儲過程中寫入複雜業務邏輯，難以除錯、版控與效能調優，建議放在應用層
• 觸發器慎用：隱式執行邏輯導致資料流向不明，大量寫入時影響效能
• 最小權限原則：應用帳號僅授予必要資料庫的 CRUD 權限，禁止 DROP/ALTER/GRANT
• 使用角色：MySQL 8.0+ 支援 CREATE ROLE，便於批次管理權限