H2 數據庫觸發器、函數及備份恢復關鍵字

在 H2 數據庫的高級應用中，觸發器關鍵字實現數據操作的自動化響應，函數定義關鍵字封裝可複用的計算邏輯，備份恢復關鍵字保障數據安全。這三類關鍵字覆蓋了自動化處理、邏輯複用和數據保障等關鍵場景，熟練運用能讓數據庫操作更智能、高效且安全。

一、觸發器相關關鍵字

觸發器就像數據庫中的 “自動響應器”，當表發生 INSERT、UPDATE、DELETE 等操作時，會自動執行預設邏輯。CREATE TRIGGER、BEFORE、AFTER、FOR EACH ROW等是觸發器的核心關鍵字。

1. 插入後觸發（AFTER INSERT）

比如在用户表插入新用户後，自動在用户詳情表創建對應記錄，避免手動同步：

-- 創建用户詳情表（用於存儲擴展信息）
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    last_login_time TIMESTAMP,
    login_count INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 創建觸發器：新增用户時自動初始化詳情記錄
CREATE TRIGGER trig_after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_profiles(user_id) VALUES(NEW.user_id);
END;

其中AFTER INSERT表示在插入操作執行後觸發，FOR EACH ROW表示對每一條插入記錄都執行觸發邏輯，NEW代表插入後的新記錄，可通過NEW.字段名獲取新數據。

2. 更新前觸發（BEFORE UPDATE）

用於在數據更新前做校驗或預處理，比如限制用户年齡不能小於 18 歲：

-- 創建觸發器：更新用户年齡前校驗
CREATE TRIGGER trig_before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户年齡不能小於18歲';
    END IF;
END;

BEFORE UPDATE表示更新操作執行前觸發，若滿足IF條件則拋出異常，阻止更新操作。SIGNAL關鍵字用於自定義錯誤信息，方便調試。

3. 刪除時觸發（AFTER DELETE）

比如刪除用户時，自動刪除其關聯的訂單和詳情記錄，保持數據一致性：

CREATE TRIGGER trig_after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    DELETE FROM orders WHERE user_id = OLD.user_id;
    DELETE FROM user_profiles WHERE user_id = OLD.user_id;
END;

OLD代表刪除前的原記錄，通過OLD.user_id獲取被刪除用户的 ID，實現關聯數據的級聯刪除。

4. 刪除觸發器

當觸發器不再需要時，用DROP TRIGGER刪除：

DROP TRIGGER IF EXISTS trig_after_user_delete;

IF EXISTS避免因觸發器不存在而報錯，更安全穩妥。

二、自定義函數關鍵字

除了系統內置函數，CREATE FUNCTION能讓你封裝自定義計算邏輯，像調用系統函數一樣複用。RETURNS、DETERMINISTIC、BEGIN、END是核心關鍵字。

1. 簡單計算函數

比如創建一個計算商品折扣價的函數，接收原價和折扣率，返回折後價：

CREATE FUNCTION calc_discount_price(original_price DECIMAL(10,2), discount_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC -- 相同輸入始終返回相同輸出，提升性能
BEGIN
    DECLARE discount_price DECIMAL(10,2);
    -- 折扣率不能超過1，否則按原價計算
    IF discount_rate > 1 THEN
        SET discount_price = original_price;
    ELSE
        SET discount_price = original_price * discount_rate;
    END IF;
    RETURN discount_price;
END;

調用方式和系統函數一致，比如查詢商品折後價（8 折）：

SELECT goods_name, original_price, calc_discount_price(original_price, 0.8) AS discount_price
FROM goods;

2. 字符串處理函數

比如創建一個將用户名首字母大寫的函數：

CREATE FUNCTION upper_first_char(username VARCHAR(50))
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    IF username IS NULL OR LENGTH(username) = 0 THEN
        RETURN username;
    END IF;
    -- 首字母大寫，其餘小寫
    RETURN CONCAT(UPPER(SUBSTRING(username, 1, 1)), LOWER(SUBSTRING(username, 2)));
END;

調用示例：

SELECT username, upper_first_char(username) AS formatted_username
FROM users;

3. 刪除自定義函數

用DROP FUNCTION刪除無用函數：

DROP FUNCTION IF EXISTS upper_first_char;

三、數據備份與恢復關鍵字

數據安全至關重要，BACKUP、RESTORE是 H2 數據庫原生的備份恢復關鍵字，操作簡單高效。

1. 備份數據庫

BACKUP DATABASE TO用於將整個數據庫備份為單個文件，支持本地路徑或網絡地址：

-- 備份數據庫到本地文件
BACKUP DATABASE TO 'D:/backup/h2_db_backup.zip';

備份文件為壓縮格式，包含所有表、數據、索引和權限信息，佔用空間小且便於傳輸。

2. 恢復數據庫

RESTORE DATABASE FROM用於從備份文件恢復數據，恢復前需確保數據庫連接模式支持（建議關閉其他連接）：

-- 從備份文件恢復數據庫
RESTORE DATABASE FROM 'D:/backup/h2_db_backup.zip';

恢復操作會覆蓋現有數據庫數據，執行前務必確認備份文件的完整性和正確性。

3. 備份單個表

若只需備份特定表，可結合EXPORT TABLE（前文提及），或用CREATE TABLE ... AS SELECT創建表備份：

-- 創建users表的備份表users_backup，包含所有數據
CREATE TABLE users_backup AS SELECT * FROM users;

這種方式適合臨時備份或部分數據備份，恢復時直接插入即可：

-- 從備份表恢復數據（先清空原表，謹慎使用）
DELETE FROM users;
INSERT INTO users SELECT * FROM users_backup;