MySQL中的确定性函数 [英] Deterministic function in mysql

问题描述

我对一个看似简单的概念感到困惑. Mysql将确定性函数定义为

I got confused with a seemingly simple concept. Mysql defines deterministic function as a function that

对于相同的输入参数总是产生相同的结果

always produces the same result for the same input parameters

据我所知，功能类似

CREATE FUNCTION foo (val INT) READS SQL DATA
BEGIN
   DECLARE retval INT;
   SET retval = (SELECT COUNT(*) FROM table_1 WHERE field_1 = val);
   RETURN retval;
END;

不是确定性的(不能保证两次调用该函数之间都不会发生delete/update/insert).同时，我看到许多功能几乎相同的功能，即根据查询结果返回值，并声明为DETERMINISTIC.看来我缺少一些基本知识.

are not deterministic (there is no guarantee that delete/update/insert does not happen between 2 calls to the function). At the same time, I saw many functions which do pretty much the same, i.e. return value based on result of queries, and declared as DETERMINISTIC. It looks like I'm missing something very basic.

有人可以澄清这个问题吗?

Could anyone clarify this issue?

谢谢.

更新 感谢那些回答(+1)的人；到目前为止，似乎已经广泛使用了DETERMINISTIC关键字.对于我来说，仍然很难相信有这么多人这样做，所以我将等待一些其他答案.

Update Thanks for those who answered(+1); so far it looks like there is a widespread misuse of DETERMINISTIC keyword. It is still hard to believe for me that so many people do it, so I'll wait a bit for other answers.

推荐答案

来自MySQL 5.0参考:

From the MySQL 5.0 Reference:

对例程性质的评估基于创建者的诚实":MySQL不会检查声明为DETERMINISTIC的例程是否没有产生不确定结果的语句.但是，错误声明例程可能会影响结果或影响性能.将不确定的例程声明为DETERMINISTIC可能会导致优化器做出错误的执行计划选择，从而导致意外结果.将确定性例程声明为NONDETERMINISTIC可能会导致不使用可用的优化，从而降低性能.在MySQL 5.0.44之前的版本中，DETERMINISTIC特性是可接受的，但优化程序不使用它.

Assessment of the nature of a routine is based on the "honesty" of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used. Prior to MySQL 5.0.44, the DETERMINISTIC characteristic is accepted, but not used by the optimizer.

因此，有了它，您可以将存储的例程标记为DETERMINISTIC，即使不是，也可以将其标记为DETERMINISTIC，但这可能会导致意外的结果或性能问题.

So there you have it, you can tag a stored routine as DETERMINISTIC even if it is not, but it might lead to unexpected results or performance problems.

这篇关于MySQL中的确定性函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！