MySQL函数使用正确特性与不当特性分别有哪些优缺点？

咱们先明确你给出的两个函数定义：一个是符合语义规范的DETERMINISTIC版本，另一个是混用了多个矛盾特性的版本。接下来咱们逐个拆解它们的优缺点：

一、使用DETERMINISTIC特性的优缺点 #

优点 #

• 优化器层面的性能提升：MySQL查询优化器会识别到这个函数是确定性的（只要输入相同，返回结果就100%一致），会自动做各种优化——比如缓存函数的计算结果，甚至直接把函数调用替换成常量值（像你的test()函数固定返回3，优化器可能直接把test()替换成3），大幅减少重复执行的开销。
• 语义清晰，维护成本低：你的函数逻辑确实是固定返回3，用DETERMINISTIC标记完全贴合实际行为，其他维护者一眼就能看懂这个函数没有副作用、结果稳定，后续修改或排查问题时不用猜逻辑。
• 支持更多核心场景：在一些对函数确定性有要求的场景下（比如创建基于函数的索引、使用基于语句的主从复制），DETERMINISTIC函数是硬性要求。如果标记错误，这些功能可能直接无法使用，甚至导致主从数据不一致。

缺点 #

• 几乎没有实质性缺点：只要你的函数逻辑确实是确定性的，用这个特性只有好处。唯一需要注意的是，如果后续修改函数逻辑（比如加入NOW()、RAND()这类非确定性逻辑），一定要同步更新特性标记，不然优化器会做出错误的优化决策。

二、使用不当特性（NOT DETERMINISTIC+NO SQL+READS SQL DATA+MODIFIES SQL DATA）的优缺点 #

首先要明确：你这里同时混用了多个完全矛盾的特性标记，MySQL实际处理时可能会忽略部分标记或按优先级处理，但这种写法本身就非常不规范，优缺点也几乎全是负面的：

优点 #

• 几乎没有任何正向作用：这种错误标记方式除了在极端的、刻意的场景下（比如你故意想让优化器不缓存结果，但你的函数明明是确定性的），对性能、维护性、功能支持都没有任何好处。

缺点 #

• 性能浪费：NOT DETERMINISTIC会告诉优化器“这个函数每次调用结果可能不一样”，优化器就会放弃所有缓存和预计算优化，哪怕你的函数实际固定返回3。这会导致每次调用都要重复执行函数逻辑，无端增加性能开销。
• 语义完全混乱：同时标记NO SQL、READS SQL DATA、MODIFIES SQL DATA是自相矛盾的：
  • NO SQL表示函数里没有任何SQL语句，但后面两个标记又说它会读写数据，这会让其他开发者完全摸不清函数的实际行为，维护难度直接拉满。
• 功能与兼容性限制：在主从复制场景中，非确定性标记可能导致主从数据不一致；另外，创建生成列索引、某些存储引擎的特殊功能也要求函数是确定性的，不当标记会直接导致这些功能无法使用。
• 潜在的稳定性风险：这种矛盾的特性组合属于MySQL的未定义行为，不同版本的MySQL可能有不同的处理逻辑——比如忽略部分标记、甚至导致函数执行异常，给系统带来隐藏的稳定性隐患。

最后再明确下这些特性的官方定义，避免混淆：

• NO SQL：例程不包含任何SQL语句；
• READS SQL DATA：例程包含读取数据的语句（如SELECT），但不包含写入数据的语句；
• MODIFIES SQL DATA：例程包含可能写入数据的语句（如INSERT、DELETE）；
• DETERMINISTIC/NOT DETERMINISTIC：标记函数是否为确定性（输入相同则输出必相同）。

内容的提问来源于stack exchange，提问作者Super Kai - Kazuya Ito