MySQL 觸發器與預存程序核心語法速查手冊

觸發器完整語法

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

• trigger_time：BEFORE / AFTER
• trigger_event：INSERT / UPDATE / DELETE
• trigger_stmt：單一語句或 BEGIN...END 區塊

觸發器行變數對照表

預存程序參數模式詳細範例

IN 參數（預設，值傳遞）

CREATE PROCEDURE demo_in(IN p_in INT)
BEGIN
  SELECT p_in;  -- 1
  SET p_in = 2;
  SELECT p_in;  -- 2
END;
SET @p_in = 1;
CALL demo_in(@p_in);
SELECT @p_in;  -- 1（外部變數未變）

OUT 參數（回傳值）

CREATE PROCEDURE demo_out(OUT p_out INT)
BEGIN
  SELECT p_out;  -- NULL
  SET p_out = 2;
  SELECT p_out;  -- 2
END;
SET @p_out = 1;
CALL demo_out(@p_out);
SELECT @p_out;  -- 2（外部變數被修改）

INOUT 參數（雙向）

CREATE PROCEDURE demo_inout(INOUT p_inout INT)
BEGIN
  SELECT p_inout;  -- 1
  SET p_inout = 2;
  SELECT p_inout;  -- 2
END;
SET @p_inout = 1;
CALL demo_inout(@p_inout);
SELECT @p_inout;  -- 2

變數定義與賦值

-- 區域變數
DECLARE l_int INT UNSIGNED DEFAULT 4000000;
DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95;
DECLARE l_date DATE DEFAULT '1999-12-31';
DECLARE l_varchar VARCHAR(255) DEFAULT 'text';

-- 賦值
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;
SELECT id, name INTO var_id, var_name FROM t3 WHERE id = 2;

使用者變數 (@var)

客戶端直接使用

SELECT 'Hello World' INTO @x;
SET @y = 'Goodbye';
SET @z = 1+2+3;

預存程序中使用

CREATE PROCEDURE GreetWorld() SELECT CONCAT(@greeting, ' World');
SET @greeting = 'Hello';
CALL GreetWorld();  -- Hello World

跨程序傳遞

CREATE PROCEDURE p1() SET @last_proc = 'p1';
CREATE PROCEDURE p2() SELECT CONCAT('Last: ', @last_proc);
CALL p1(); CALL p2();  -- Last: p1

註釋風格

• -- 單行註釋
• /* ... */ 多行註釋（C 風格）

預存程序查詢與管理

查看程序列表

SELECT name FROM mysql.proc WHERE db = 'db_name';
SELECT routine_name FROM information_schema.routines WHERE routine_schema = 'db_name';
SHOW PROCEDURE STATUS WHERE db = 'db_name';

查看程序定義

SHOW CREATE PROCEDURE db_name.proc_name;

修改與刪除

ALTER PROCEDURE proc_name [characteristics];
DROP PROCEDURE proc_name;

流程控制語句範例

變數作用域

CREATE PROCEDURE proc3()
BEGIN
  DECLARE x1 VARCHAR(5) DEFAULT 'outer';
  BEGIN
    DECLARE x1 VARCHAR(5) DEFAULT 'inner';
    SELECT x1;  -- inner
  END;
  SELECT x1;  -- outer
END;

IF-THEN-ELSE

IF var = 0 THEN INSERT INTO t VALUES(17);
ELSEIF param = 0 THEN UPDATE t SET s1 = s1 + 1;
ELSE UPDATE t SET s1 = s1 + 2; END IF;

CASE

CASE var
  WHEN 0 THEN INSERT INTO t VALUES(17);
  WHEN 1 THEN INSERT INTO t VALUES(18);
  ELSE INSERT INTO t VALUES(19);
END CASE;

WHILE 迴圈

WHILE var < 6 DO
  INSERT INTO t VALUES(var);
  SET var = var + 1;
END WHILE;

REPEAT 迴圈

REPEAT
  INSERT INTO t VALUES(v);
  SET v = v + 1;
UNTIL v >= 5 END REPEAT;

LOOP + LEAVE

LOOP_LABEL: LOOP
  INSERT INTO t VALUES(v);
  SET v = v + 1;
  IF v >= 5 THEN LEAVE LOOP_LABEL; END IF;
END LOOP;

ITERATE 跳過本次迴圈

LOOP_LABEL: LOOP
  IF v = 3 THEN SET v = v + 1; ITERATE LOOP_LABEL; END IF;
  INSERT INTO t VALUES(v);
  SET v = v + 1;
  IF v >= 5 THEN LEAVE LOOP_LABEL; END IF;
END LOOP;

常用內建函數速查

字串函數

CONCAT(str1, str2...)      -- 連接
SUBSTRING(str, pos, len)   -- 子字串（位置從 1 開始）
TRIM([BOTH|LEADING|TRAILING] [pad] FROM str)
REPLACE(str, from_str, to_str)
LEFT(str, len), RIGHT(str, len)
LENGTH(str), CHARSET(str)
LOCATE(substr, str [, pos])

數學函數

ABS(x), CEILING(x), FLOOR(x), ROUND(x [,d])
MOD(n, m), POWER(x, y), RAND([seed])
CONV(num, from_base, to_base)  -- 進制轉換
HEX(num/str)                   -- 十六進制/ASCII 碼

日期時間函數

NOW(), CURRENT_DATE(), CURRENT_TIME()
DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)
DATEDIFF(date1, date2)
DATE_FORMAT(date, format)
STR_TO_DATE(str, format)
UNIX_TIMESTAMP(), FROM_UNIXTIME()
DAY(), MONTH(), YEAR(), HOUR(), MINUTE(), SECOND()
LAST_DAY(date), DAYOFWEEK(), DAYOFYEAR()