MySQL 資料庫物件全解析：索引、約束、視圖、觸發器與存儲程序

學習目標

• 掌握常見資料庫物件的類型與用途
• 學會建立與管理各類資料物件

常見資料庫物件分類

資料庫層級物件

• Database/Schema：邏輯容器，用於業務隔離與資源隔離
• Table：核心儲存結構

表層級物件

• Index（索引）：提升查詢效率的資料目錄
• Constraint（約束）：資料完整性規則
• View/Trigger/Function/Procedure：進階邏輯封裝

索引深度解析

什麼是索引

索引是資料庫中資料的「目錄」，獨立於資料本身存在。主要目的在於提高查詢效率。索引通常採用 B+Tree 結構，資料變更時需同步維護索引結構。

索引類型與建立語法

CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option]

• UNIQUE：唯一索引，適用於 ID、手機號等唯一欄位
• SPATIAL：空間索引，適用於地理位置查詢（如搜尋周邊）

兩種建立索引方式

-- 方式一：CREATE INDEX
CREATE INDEX idx_v ON vc(v);

-- 方式二：ALTER TABLE
ALTER TABLE vc ADD KEY idx_c (c);

查看索引

SHOW INDEX FROM vc;
SHOW CREATE TABLE vc;

在 MySQL 中，INDEX 與 KEY 同義，皆代表索引。

約束機制

唯一約束

• 本質是特殊索引，可由單欄或多欄組成
• 可在建表時定義，也可事後新增
• 主鍵是特殊的唯一約束

外鍵約束

將兩張表透過特定欄位關聯，確保參照完整性。例如：訂單表的 userid 必須存在於用戶表中。

建立外鍵語法

ALTER TABLE `order` ADD CONSTRAINT fk_uid FOREIGN KEY (userid) REFERENCES `user`(userid) ON DELETE RESTRICT ON UPDATE CASCADE;

外鍵行為模式

模式	說明
RESTRICT / NO ACTION	限制父表操作，若子表有關聯記錄則拒絕
CASCADE	級聯操作，父表更新/刪除時同步子表
SET NULL	父表操作時，子表關聯欄位設為 NULL

使用注意事項

• 必須使用 InnoDB 儲存引擎
• 關聯欄位型別必須完全一致
• 父表關聯欄位需有索引
• 約束名稱在整個資料庫中唯一

暫時關閉外鍵檢查

SET FOREIGN_KEY_CHECKS = 0; -- 關閉
-- 執行大量資料匯入/結構變更
SET FOREIGN_KEY_CHECKS = 1; -- 開啟

視圖

核心價值

• 將複雜查詢封裝為虛擬表，簡化存取
• 隱藏底層表結構，提升安全性
• 實現欄位級權限控制

建立範例

CREATE VIEW order_view AS SELECT * FROM `order` WHERE status = 1;

觸發器

定義與觸發時機

綁定在表上的特殊程式，當發生 INSERT/UPDATE/DELETE 事件時自動執行。每張表每種事件僅能定義一個觸發器。

應用場景

• 資料修正與驗證
• 資料遷移與同步
• 實現複雜業務邏輯

基本語法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON tbl_name FOR EACH ROW
trigger_body

關鍵要點

• 使用 DELIMITER // 變更結束符號
• 透過 NEW 與 OLD 存取變更前後的資料
• 執行順序：BEFORE 觸發器 → 行操作 → AFTER 觸發器
• 事務表中任一步驟失敗將整體回滾；非事務表已提交資料無法回滾

效能與限制

• 有效能損耗，謹慎使用
• 不支援遞迴觸發
• Row 格式主從複制下，從庫不執行觸發器

存儲程序

定義與優勢

儲存在資料庫端的 SQL 程式模組，支援流程控制、變數、參數傳遞。

• 減少網路傳輸
• 提升安全性（屏蔽直接操作表）
• 便於審計與維護

基本語法

CREATE PROCEDURE sp_name ([IN|OUT|INOUT] param_name type, ...)
[CHARACTERISTIC ...]
routine_body

參數模式

• IN：輸入參數
• OUT：輸出參數
• INOUT：輸入輸出參數

呼叫流程

SET @total = 5, @res = 0;
CALL proc_test1(@total, @res);
SELECT @res;

自定義函數

與存儲程序差異

• 必須有 RETURN 回傳值
• 使用方式類似內建函數：SELECT func(val)
• 因可能在逐列掃描中呼叫，需特別注意效能

基本語法

CREATE FUNCTION func_name (param_name type, ...)
RETURNS return_type
routine_body

權限管理

• 建立程序/函數需 CREATE ROUTINE 權限
• 修改/刪除需 ALTER ROUTINE
• 執行需 EXECUTE 權限

總結重點

• 索引建立與查看指令
• 唯一約束、外鍵約束的四種模式
• 視圖建立與權限隔離
• 觸發器：單表單事件唯一、DELIMITER、NEW/OLD
• 存儲程序：DECLARE、參數傳遞、流程控制、三步驟呼叫
• 函數：必須回傳值、SELECT 調用