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);
| 方法 | 操作 | 優缺點 |
|---|---|---|
| 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 |
| 關鍵字 | 含義 | 典型場景 |
|---|---|---|
DETERMINISTIC | 相同輸入必產生相同輸出 | 純計算函數(如 ADD_DAYS) |
READS SQL DATA | 讀取資料但不修改 | 查詢聚合函數(如本例) |
NO SQL | 不包含 SQL 語句 | 純邏輯運算(如字串處理) |
MODIFIES SQL DATA | 修改資料(INSERT/UPDATE/DELETE) | 函數中禁止使用,僅存儲過程可用 |