MySQL 進階核心技術：觸發器、模糊查詢、存儲過程與內建函數大全

MySQL 進階核心技術：觸發器、模糊查詢、存儲過程與內建函數大全

實戰專案中常因細節生疏導致效能損耗或邏輯錯誤。本文系統整理觸發器全生命週期、LIKE/REGEXP 模糊匹配規則、存儲過程參數/變量/流程控制、以及 60+ 內建函數分類速查，作為開發隨手查閱手冊。

一、觸發器：表級事件自動化響應

1. 核心語法

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

2. 實戰案例：跨表同步插入

-- yyd_table 插入後，自動向 yyd_name 寫入固定值
CREATE TRIGGER yyd_tri AFTER INSERT ON yyd_table
FOR EACH ROW BEGIN
    INSERT INTO yyd_name VALUES (NULL, 'name_yyd');
END;

3. 修改 NEW 值（BEFORE 觸發器）

CREATE TRIGGER yyd_tri1 BEFORE INSERT ON yyd_name
FOR EACH ROW BEGIN
    SET NEW.name = '4';
END;

4. 刪除觸發器

DROP TRIGGER yyd_tri;

5. 遞迴調用陷阱與解法

觸發器內對同表再做 INSERT/UPDATE 會導致無限遞迴。錯誤寫法：

CREATE TRIGGER test BEFORE UPDATE ON test FOR EACH ROW
    UPDATE test SET NEW.updateTime = NOW() WHERE id=NEW.ID;  -- 觸發自身

正確寫法：直接 SET NEW.updateTime = NOW();，不再發動 DML。

二、模糊查詢：LIKE 與 REGEXP 完整規則

1. LIKE 通配符

2. REGEXP 正則表達式

SELECT * FROM user WHERE u_name REGEXP '三|貓';  -- OR 邏輯
SELECT * FROM user WHERE u_name REGEXP BINARY 'JetPack.000';  -- 區分大小寫

三、存儲過程：邏輯封裝與複雜流程控制

1. 定義與分隔符

DELIMITER //
CREATE PROCEDURE proc1(OUT s INT)
BEGIN
    SELECT COUNT(*) INTO s FROM user;
END //
DELIMITER ;

2. 參數模式

• IN：輸入參數，預設，內部修改不回傳；
• OUT：輸出參數，內部賦值可回傳；
• INOUT：雙向參數。

3. 變量聲明與作用域

DECLARE var_name TYPE [DEFAULT value];
SET var_name = expr;

-- 會話變量（跨過程可見）
SET @x = 'Hello';
SELECT @x;

區塊內同名變量遮蔽外層，離開區塊自動銷毀，需透過 OUT 參數或會話變量持久化。

4. 流程控制語句全集

-- IF-THEN-ELSE
IF condition THEN ... ELSEIF ... ELSE ... END IF;

-- CASE
CASE var WHEN 0 THEN ... WHEN 1 THEN ... ELSE ... END CASE;

-- WHILE
WHILE var < 6 DO ... END WHILE;

-- REPEAT (類 do-while)
REPEAT ... UNTIL condition END REPEAT;

-- LOOP + LEAVE/ITERATE
loop_label: LOOP
    IF v >= 5 THEN LEAVE loop_label; END IF;
    IF v = 3 THEN SET v=v+1; ITERATE loop_label; END IF;
    ...
END LOOP;

5. 管理命令

-- 查詢
SELECT name FROM mysql.proc WHERE db='dbname';
SHOW PROCEDURE STATUS WHERE db='dbname';

-- 刪除
DROP PROCEDURE proc_name;

四、內建函數速查表（60+）

字串函數

CONCAT、INSTR、LEFT/RIGHT、LENGTH、LOCATE、LPAD/RPAD、LTRIM/RTRIM/TRIM、REPEAT、REPLACE、STRCMP、SUBSTRING、UCASE/LCASE、SPACE

數學函數

ABS、BIN、CEILING/FLOOR、CONV、FORMAT、HEX、LEAST、MOD、POWER、RAND、ROUND、SQRT

日期時間函數

ADDTIME、CONVERT_TZ、CURRENT_DATE/TIME/TIMESTAMP、DATE、DATE_ADD/SUB、DATE_FORMAT、DATEDIFF、DAY/DAYNAME/DAYOFWEEK/DAYOFYEAR、EXTRACT、MAKEDATE/MAKETIME、MONTHNAME、NOW、SEC_TO_TIME/TIME_TO_SEC、TIMEDIFF、WEEK、YEAR、HOUR/MINUTE/SECOND/MICROSECOND、LAST_DAY、STR_TO_DATE

五、觸發器調用存儲過程

CREATE TRIGGER ... BEGIN CALL showChildLst(NEW.pn, NEW.pa); END;