掌握視圖、觸發器、存儲過程、自定義函數、事務控制、索引類型、流程控制，從「會寫 SQL」進階到「寫好數據庫程序」。
-- 創建
CREATE VIEW v_high_value_orders AS
SELECT o.id, o.user_id, o.amount, u.name
FROM orders o JOIN users u ON o.user_id=u.id
WHERE o.amount > 1000;
-- 使用（當表查）
SELECT * FROM v_high_value_orders WHERE name LIKE '張%';
-- 修改
ALTER VIEW v_high_value_orders AS SELECT ...;
-- 刪除
DROP VIEW v_high_value_orders;
限制：不可直接 INSERT/UPDATE/DELETE（除非滿足可更新視圖嚴格條件），主要用於查詢封裝、權限隔離、業務邏輯復用。
-- 語法：BEFORE/AFTER + INSERT/UPDATE/DELETE
DELIMITER //
CREATE TRIGGER trg_audit_user_insert
AFTER INSERT ON users FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, action, new_data, create_time)
VALUES ('users', 'INSERT', JSON_OBJECT('id', NEW.id, 'name', NEW.name), NOW());
END//
DELIMITER ;
-- 刪除
DROP TRIGGER trg_audit_user_insert;
| 觸發時機 | NEW | OLD | 典型用途 |
|---|---|---|---|
| BEFORE INSERT | 新行 | 無 | 數據校驗、補全默認值、生成 ID |
| AFTER INSERT | 新行 | 無 | 日志審計、衍生表同步、發送通知 |
| BEFORE UPDATE | 新值 | 舊值 | 防篡改關鍵字段、記錄變更前值 |
| AFTER UPDATE | 新值 | 舊值 | 異步同步、緩存失效、審計 |
| BEFORE DELETE | 無 | 舊行 | 軟刪除攔截、關聯檢查 |
| AFTER DELETE | 無 | 舊行 | 清理關聯數據、審計 |
注意：觸發器內不可調用事務控制、不可對同表再做觸發動作（遞歸），性能敏感業務慎用。
-- 無參
DELIMITER //
CREATE PROCEDURE sp_clean_expired_logs()
BEGIN
DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
END//
DELIMITER ;
-- 有參（IN/OUT/INOUT）
DELIMITER //
CREATE PROCEDURE sp_transfer(
IN from_id BIGINT, IN to_id BIGINT, IN amount DECIMAL(10,2),
OUT result_code TINYINT, OUT result_msg VARCHAR(100)
)
BEGIN
DECLARE from_balance DECIMAL(10,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET result_code = 1; SET result_msg = '系統異常';
END;
START TRANSACTION;
SELECT balance INTO from_balance FROM accounts WHERE id=from_id FOR UPDATE;
IF from_balance < amount THEN
SET result_code = 2; SET result_msg = '餘額不足'; ROLLBACK;
ELSE
UPDATE accounts SET balance=balance-amount WHERE id=from_id;
UPDATE accounts SET balance=balance+amount WHERE id=to_id;
COMMIT;
SET result_code = 0; SET result_msg = '成功';
END IF;
END//
DELIMITER ;
-- 調用
CALL sp_clean_expired_logs();
SET @rc=0; SET @rm=''; CALL sp_transfer(1,2,100,@rc,@rm); SELECT @rc,@rm;
DELIMITER //
CREATE FUNCTION fn_calc_age(birth_date DATE) RETURNS INT
DETERMINISTIC
BEGIN
RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END//
DELIMITER ;
-- SQL 中使用
SELECT name, fn_calc_age(birthday) AS age FROM users WHERE fn_calc_age(birthday) > 18;
區別：函數必須有 RETURN、只能返回單值、可在 SELECT/WHERE 中用；過程無返回值、CALL 獨立調用、可做 DML/DDL、支持 OUT 參數。
START TRANSACTION; -- 或 BEGIN;
INSERT INTO orders ...;
UPDATE accounts SET balance=balance-100 WHERE id=1;
-- 成功
COMMIT;
-- 失敗
ROLLBACK;
-- 保存點
SAVEPOINT sp1;
UPDATE ...;
ROLLBACK TO SAVEPOINT sp1; -- 回滾到 sp1
RELEASE SAVEPOINT sp1;
-- 自動提交控制
SET autocommit=0; -- 關閉，需手動 COMMIT
SET autocommit=1; -- 開啟（默認）
DDL 隱式提交：CREATE/DROP/ALTER/TRUNCATE 會隱式提交當前事務，謹慎混用。
| 類型 | 創建語法 | 約束 | NULL | 適用場景 |
|---|---|---|---|---|
| 主鍵 | PRIMARY KEY (id) | 唯一 + NOT NULL | ❌ | 每表一個，聚簇索引 |
| 唯一 | UNIQUE INDEX uk_email (email) | 唯一 | ✅ (多 NULL) | 業務唯一鍵：郵箱、手機、身份證 |
| 普通 | INDEX idx_name (name) | 無 | ✅ | 高頻 WHERE/JOIN/ORDER BY 列 |
| 組合 | INDEX idx_a_b (a,b) | 無 | ✅ | 多列聯合查詢，遵循最左前綴 |
關鍵操作：SHOW INDEX FROM t;、DROP INDEX idx ON t;、ALTER TABLE t ADD INDEX idx(col);
-- IF 分支
IF condition THEN ... ELSEIF condition THEN ... ELSE ... END IF;
-- CASE 分支
CASE expr WHEN val1 THEN ... WHEN val2 THEN ... ELSE ... END CASE;
-- WHILE 循環
WHILE i < 10 DO ... END WHILE;
-- REPEAT 循環（至少一次）
REPEAT ... UNTIL condition END REPEAT;
-- LOOP + LEAVE/ITERATE
loop_label: LOOP ... IF condition THEN LEAVE loop_label; END IF; END LOOP;
-- 動態 SQL
PREPARE stmt FROM 'SELECT * FROM t WHERE id > ?';
SET @id = 10;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;
熟練掌握這些可編程對象，能將業務邏輯下沉數據庫層，減少應用層複雜度，提升數據一致性與處理性能。
