MySQL 存儲過程、函數與觸發器開發實戰速查

MySQL 存儲過程、函數與觸發器開發實戰速查

一、常用內置函數分類速查

字符串函數

CONCAT(str1, str2, ...)           -- 拼接
INSTR(str, substr)                -- 子串首次出現位置 (1-based)
LOCATE(substr, str, [pos])        -- 同 INSTR，可指定起始位置
LEFT(str, len), RIGHT(str, len)   -- 取左/右 len 字符
SUBSTRING(str, pos, [len])        -- 截取 (pos 從 1 開始)
TRIM([BOTH|LEADING|TRAILING] [pad] FROM str) -- 去除首尾指定字符
REPLACE(str, from_str, to_str)    -- 替換
LPAD/RPAD(str, len, pad)          -- 左/右填充至指定長度
UCASE/LCASE(str)                  -- 大/小寫轉換
STRCMP(str1, str2)                -- 比較 (-1/0/1)

數學函數

ABS(x), CEILING(x), FLOOR(x), ROUND(x, [d])  -- 絕對值/向上/向下/四捨五入
MOD(n, m), POWER(x, y), SQRT(x)              -- 取模/冪/開方
RAND([seed])                                  -- 隨機數
LEAST(v1, v2, ...), GREATEST(v1, v2, ...)    -- 最小/最大值
CONV(num, from_base, to_base)                 -- 進制轉換
HEX(N_or_S)                                   -- 十六進制 (數字或字符串 ASCII 碼)

日期時間函數

NOW(), CURRENT_TIMESTAMP()        -- 當前日期時間
CURDATE(), CURTIME()              -- 當前日期/時間
DATE_ADD(date, INTERVAL expr unit), DATE_SUB(...) -- 日期加減
DATEDIFF(date1, date2)            -- 日期差 (天)
DATE_FORMAT(date, fmt), STR_TO_DATE(str, fmt)   -- 格式化/解析
EXTRACT(unit FROM date)           -- 提取年/月/日/時/分/秒
DAY(), MONTH(), YEAR(), HOUR(), MINUTE(), SECOND()
DAYNAME(), MONTHNAME(), DAYOFWEEK(), DAYOFYEAR()
LAST_DAY(date)                    -- 月最後一天
MAKEDATE(year, dayofyear), MAKETIME(h,m,s)
SEC_TO_TIME(sec), TIME_TO_SEC(time)
TIMEDIFF(dt1, dt2)                -- 時間差
CONVERT_TZ(dt, from_tz, to_tz)    -- 時區轉換
ADDTIME(dt, time_interval)

INTERVAL 單位：SECOND, MINUTE, HOUR, DAY, MONTH, YEAR, MINUTE_SECOND, HOUR_MINUTE, HOUR_SECOND, DAY_HOUR, DAY_MINUTE, DAY_SECOND。

二、存儲過程

創建

DELIMITER //
DROP PROCEDURE IF EXISTS `proc_test`//
CREATE PROCEDURE `proc_test`(IN table_name VARCHAR(20), IN num INT)
BEGIN
  SET @sql = CONCAT('SELECT * FROM ', table_name, ' LIMIT ', num);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

調用

CALL proc_test('user', 20);

刪除

DROP PROCEDURE IF EXISTS proc_test;

三、觸發器

語法結構

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- trigger_body
END;

實例：同步輔助表

-- INSERT 同步
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
  INSERT INTO tab2(tab2_id) VALUES (NEW.tab1_id);
END;

-- DELETE 同步
DROP TRIGGER IF EXISTS t_afterdelete_on_tab1;
CREATE TRIGGER t_afterdelete_on_tab1
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
  DELETE FROM tab2 WHERE tab2_id = OLD.tab1_id;
END;

• NEW：新行數據 (INSERT/UPDATE 可用)。
• OLD：舊行數據 (UPDATE/DELETE 可用)。

刪除觸發器

DROP TRIGGER [schema_name.]trigger_name;

四、權限與限制提示

• 創建觸發器需 TRIGGER 權限 (通常需 SUPER/ROOT)。
• 觸發器體內不能使用顯式/隱式提交的語句 (如 START TRANSACTION, COMMIT, DDL 等)。
• 同一張表、同一事件、同一時機 (BEFORE/AFTER) 只能有一個觸發器 (MySQL 5.7+ 支持多個，需指定 FOLLOWS/PRECEDES)。