MySQL：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法

一、背景与问题

在MySQL中创建存储函数时，若未正确声明DETERMINISTIC、NO SQL或READS SQL DATA属性，会抛出以下错误：

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its definition.

该错误源于MySQL对存储函数的严格限制。根据MySQL官方文档，存储函数必须声明以下三类属性之一：

1. DETERMINISTIC：函数在相同输入下始终返回相同结果（如数学计算）
2. NO SQL：函数不执行任何SQL语句（如纯计算）
3. READS SQL DATA：函数读取数据库数据（如查询操作）

未声明任何属性时，MySQL会认为该函数可能修改数据库状态或引入不可预测行为，从而拒绝创建。该错误在实际开发中频繁出现，尤其是在涉及业务逻辑计算的场景中。

二、基本原理

1. 属性含义详解

注意：CONTAINS SQL和MODIFIES SQL DATA属于更高级的属性，通常不建议在存储函数中使用，因为它们可能导致数据不一致。

2. 为什么需要这些属性？

MySQL要求存储函数声明属性的原因包括：

• 避免副作用：确保函数不会意外修改数据
• 缓存优化：DETERMINISTIC函数可被缓存以提升性能
• 事务安全：防止函数在事务中引发不可预期的变更

三、环境准备

确保MySQL版本支持存储函数（5.0+）。创建测试表和函数前，先准备以下环境：

-- 创建测试表
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    value VARCHAR(255)
);

-- 插入测试数据
INSERT INTO test_table (id, value) VALUES
(1, 'A'), (2, 'B'), (3, 'C');

四、核心实现

1. 错误示例：未声明属性

DELIMITER $$
CREATE FUNCTION calculate_length(input VARCHAR(255)) 
RETURNS INT
BEGIN
    RETURN LENGTH(input);
END $$
DELIMITER ;

错误原因：LENGTH()是MySQL内置函数，属于DETERMINISTIC，但未显式声明属性，导致报错。

2. 正确示例：声明DETERMINISTIC

DELIMITER $$
CREATE FUNCTION calculate_length(input VARCHAR(255)) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN LENGTH(input);
END $$
DELIMITER ;

关键代码解释：

• DETERMINISTIC声明：明确函数的确定性行为
• BEGIN...END：函数体定义
• RETURNS INT：函数返回类型

3. 正确示例：声明READS SQL DATA

DELIMITER $$
CREATE FUNCTION get_value_count()
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM test_table;
    RETURN count;
END $$
DELIMITER ;

关键代码解释：

• READS SQL DATA：函数会查询test_table表
• DECLARE：声明局部变量
• SELECT INTO：将查询结果赋值给变量

五、完整案例

场景：计算某个字段的平均值

需求：创建一个函数，计算test_table中value字段的平均长度。

实现步骤：

1. 创建函数（声明READS SQL DATA）：

DELIMITER $$
CREATE FUNCTION avg_value_length()
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE total INT;
    DECLARE count INT;
    DECLARE result DECIMAL(10,2);
    
    SELECT SUM(LENGTH(value)), COUNT(*) INTO total, count FROM test_table;
    SET result = total / count;
    RETURN result;
END $$
DELIMITER ;

2. 调用函数：

SELECT avg_value_length() AS avg_length;

输出示例：

+------------+
| avg_length |
+------------+
| 1.00       |
+------------+

性能优化：

• 使用READS SQL DATA时，可添加SQL_NO_CACHE优化查询：

  SELECT SUM(LENGTH(value)), COUNT(*) SQL_NO_CACHE INTO total, count FROM test_table;

六、源码解析

MySQL的存储函数定义在sql/sql_yacc.yy中，关键逻辑如下：

// 存储函数定义处理
case FUNCTION_DEFINITION: {
    // 检查是否声明了DETERMINISTIC/NO SQL/READS SQL DATA
    if (!has_deterministic && !has_no_sql && !has_reads_sql_data) {
        my_error(ER_WRONG_FUNCTION_DEFINITION, MYF(ME_FATAL));
        return 1;
    }
    // 继续处理函数体
}

未声明任何属性时，会抛出ER_WRONG_FUNCTION_DEFINITION错误。

七、进阶使用

1. 属性选择策略

2. 复杂函数设计

DELIMITER $$
CREATE FUNCTION calculate_sum_with_condition()
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE sum_val INT DEFAULT 0;
    DECLARE val VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT value FROM test_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET val = NULL;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO val;
        IF val IS NULL THEN
            LEAVE read_loop;
        END IF;
        SET sum_val = sum_val + LENGTH(val);
    END LOOP;
    CLOSE cur;
    RETURN sum_val;
END $$
DELIMITER ;

注意事项：

• 使用游标时需声明READS SQL DATA
• 避免在函数中使用SELECT ... INTO导致隐式事务

八、性能与工程实践

1. 性能优化方法

• 缓存：DETERMINISTIC函数可被缓存，减少重复计算
• 索引：在READS SQL DATA函数中，对查询字段添加索引
• 避免复杂逻辑：函数体应保持简单，避免嵌套过多逻辑

2. 安全风险分析

• SQL注入：若函数中使用字符串拼接，需用CONCAT()替代+操作符
• 数据一致性：函数中若修改数据，需确保事务正确处理
• 权限控制：限制函数执行权限，防止未授权访问

3. 异常处理

DELIMITER $$
CREATE FUNCTION safe_divide(a DECIMAL(10,2), b DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE result DECIMAL(10,2);
    IF b = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Division by zero';
    END IF;
    SET result = a / b;
    RETURN result;
END $$
DELIMITER ;

九、常见问题与踩坑

1. 常见错误及解决办法

2. 常见陷阱

• 多线程环境下的缓存失效：DETERMINISTIC函数缓存可能失效，需依赖MySQL版本特性
• 函数重名覆盖：确保函数名唯一，避免与内置函数冲突
• 参数类型不匹配：严格检查函数参数类型与调用时的类型一致性

十、最佳实践

1. 使用建议

• 优先使用DETERMINISTIC：对于计算密集型函数，可显著提升性能
• 避免在函数中使用游标：可能引发死锁或性能问题
• 对敏感操作添加校验：如非空检查、权限校验
• 定期审查函数逻辑：确保符合业务需求且无副作用

2. 避免使用场景

• 需要修改数据的场景：应使用存储过程而非存储函数
• 复杂业务逻辑：可能导致维护困难，建议拆分为多个函数
• 涉及大量数据处理：应通过SQL优化而非函数处理

十一、总结

MySQL的存储函数属性声明是保障数据库稳定性和性能的关键机制。通过合理选择DETERMINISTIC、NO SQL或READS SQL DATA属性，可以避免常见错误并提升函数的可靠性。在实际开发中，需根据业务需求权衡使用场景，避免在函数中执行可能引发副作用的操作。对于复杂业务逻辑，建议拆分为多个函数或采用其他更合适的实现方式，以确保系统的可维护性和稳定性。