萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 用戶自定義函數(UDF)實戰與「DETERMINISTIC」錯誤解決方案

MySQL 函數預設要求宣告確定性特徵,否則啟用二進位日誌時會報錯。本文示範一個聚合用戶名的函數,並給出三種解決「This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA...」錯誤的方法。

前置檢查:函數建立權限

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| log_bin_trust_function_creators         | OFF   |  -- 需開啟
+-----------------------------------------+-------+

函數範例:按狀態聚合用戶真實姓名

USE app02;
DELIMITER //

DROP FUNCTION IF EXISTS `testhanshu`;

CREATE FUNCTION `testhanshu`(`tustate` INT)
RETURNS VARCHAR(2000)
-- 關鍵:宣告特徵,任選一項即可避開錯誤
DETERMINISTIC
-- 或 READS SQL DATA
-- 或 NO SQL
BEGIN
    DECLARE oneName VARCHAR(200) DEFAULT '';
    DECLARE allNames VARCHAR(2000) DEFAULT '';
    DECLARE done INT DEFAULT FALSE;

    DECLARE cur CURSOR FOR
        SELECT utruename FROM tsys_user WHERE ustate = tustate;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    REPEAT
        FETCH cur INTO oneName;
        IF NOT done THEN
            SET oneName = CONCAT(oneName, ';');
            SET allNames = CONCAT(allNames, oneName);
        END IF;
    UNTIL done END REPEAT;

    CLOSE cur;
    RETURN allNames;
END//

DELIMITER ;

-- 測試
SELECT testhanshu(1);

三種解決「DETERMINISTIC」錯誤的方法

方法操作優缺點
1. 會話級開啟信任SET GLOBAL log_bin_trust_function_creators = 1;簡單、立即生效;不持久化,重啟失效。降低二進位日誌安全性(允許不確定函數寫入 binlog)。
2. 啟動參數固化啟動命令加 --log-bin-trust-function-creators=1 或 my.cnf [mysqld] 區段加 log_bin_trust_function_creators=1持久化;同樣降低 binlog 安全性。
3. 正確宣告特徵(推薦)函數定義加 DETERMINISTIC / READS SQL DATA / NO SQL最佳實踐:不降低安全性,明確告知優化器函數行為。
- 只讀資料 → READS SQL DATA
- 不讀寫資料 → NO SQL
- 相同輸入必得相同輸出 → DETERMINISTIC

特徵關鍵字對照

游標在函數中的注意事項

  • 必須宣告 CONTINUE HANDLER FOR NOT FOUND 避免游標讀完拋出異常中斷函數。
  • REPEAT ... UNTIL done END REPEAT 比 WHILE 更適合「至少一次」的游標讀取模式。
  • 函數內游標僅支援 READ_ONLY、FORWARD_ONLY。

遵循「宣告正確特徵 > 修改全域參數」原則,即能安全、合規地使用 MySQL UDF 擴展資料庫邏輯。

copyright © 萬盛學電腦網 all rights reserved
關鍵字含義典型場景
DETERMINISTIC相同輸入必產生相同輸出純計算函數(如 ADD_DAYS)
READS SQL DATA讀取資料但不修改查詢聚合函數(如本例)
NO SQL不包含 SQL 語句純邏輯運算(如字串處理)
MODIFIES SQL DATA修改資料(INSERT/UPDATE/DELETE)函數中禁止使用,僅存儲過程可用