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

建立 MySQL 用戶自定義函數 (UDF) 時常遇到 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA... 錯誤。本文提供完整的函數建立範例（含游標迴圈拼接字串），並詳細解析三種解決方案的優劣與適用場景。

一、前置檢查：啟用函數建立權限

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
-- Value = OFF 時需開啟

二、函數範例：根據用戶狀態拼接真實姓名

USE app02;
DELIMITER //

DROP FUNCTION IF EXISTS `testhanshu`;

CREATE FUNCTION `testhanshu`(`tustate` INT)
RETURNS VARCHAR(2000)
BEGIN
    DECLARE oneAddr VARCHAR(200) DEFAULT '';
    DECLARE allAddr VARCHAR(2000) DEFAULT '';
    DECLARE done INT DEFAULT FALSE;

    DECLARE curl CURSOR FOR 
        SELECT utruename FROM tsys_user WHERE ustate = tustate;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN curl;

    REPEAT
        FETCH curl INTO oneAddr;
        IF NOT done THEN
            SET oneAddr = CONCAT(oneAddr, ';');
            SET allAddr = CONCAT(allAddr, oneAddr);
        END IF;
    UNTIL done END REPEAT;

    CLOSE curl;
    RETURN allAddr;
END;
//

DELIMITER ;

-- 測試呼叫
SELECT testhanshu(1);

三、錯誤成因與三種解法

錯誤訊息：

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

根因：啟用 binlog 時，MySQL 要求函數聲明確定性特性，確保主從同步一致性。

方案 1：全域變數開啟信任（最快、最不安全）

SET GLOBAL log_bin_trust_function_creators = 1;

• 即時生效，無需重啟
• 風險：允許建立不確定性函數，可能導致主從資料不一致
• 適用：開發/測試環境、單機部署、無主從同步

方案 2：啟動參數永久生效（較安全）

編輯啟動腳本或服務配置，加入參數：

--log-bin-trust-function-creators=1

• 重啟後生效，持久化
• 同方案 1 風險，但避免運行時誤改

方案 3：配置檔 my.cnf 設定（推薦生產）

[mysqld]
log-bin-trust-function-creators=1

• 重啟生效，版本可控、易審計
• 同樣風險，但符合基礎設施即代碼 (IaC) 原則

四、最佳實踐：正確聲明函數特性（根治）

與其開啟信任開關，不如在函數定義中明確宣告特性：

CREATE FUNCTION `testhanshu`(`tustate` INT)
RETURNS VARCHAR(2000)
DETERMINISTIC          -- 相同輸入必產生相同輸出
READS SQL DATA           -- 僅讀取資料，不修改
BEGIN
    -- 函數本體
END;

• DETERMINISTIC：優化器可快取結果，主從同步安全
• READS SQL DATA：告知引擎僅讀操作
• NO SQL：完全不存取資料（純計算）
• MODIFIES SQL DATA：會修改資料（函數中禁止，僅程序可用）

建議：優先加上 DETERMINISTIC READS SQL DATA，僅在確無法滿足（如呼叫 UUID()、RAND()）時才考慮開啟信任開關。

五、函數效能提醒

• 函數內使用游標逐列處理效能極差，大數據量請改用 GROUP_CONCAT：

SELECT GROUP_CONCAT(utruename SEPARATOR ';') 
FROM tsys_user WHERE ustate = 1;

• 函數在 SQL 中逐列調用會嚴重拖慢查詢，能用 JOIN、子查詢、視圖替代時盡量不用 UDF

理解二進制日誌與函數確定性的關係，正確聲明特性而非盲目開啟信任開關，才是專業 DBA 的態度。