MySQL mysql中的确定性函数

Deterministic function in mysql

提问by a1ex07

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

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

always produces the same result for the same input parameters

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

So in my understanding, functions like

所以在我的理解中，函数就像

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;

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.

不是确定性的（不能保证在两次调用函数之间不会发生删除/更新/插入）。同时，我看到许多功能几乎相同，即根据查询结果返回值，并声明为DETERMINISTIC. 看起来我缺少一些非常基本的东西。

Could anyone clarify this issue?

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

Thanks.

谢谢。

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

更新感谢那些回答（+1）；到目前为止，看起来DETERMINISTIC关键字被广泛滥用。对我来说仍然很难相信有这么多人这样做，所以我会等待其他答案。

采纳答案by Xint0

From the MySQL 5.0 Reference:

来自 MySQL 5.0 参考：

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.

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

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

所以你有了它，你可以标记一个存储的例程，DETERMINISTIC即使它不是，但它可能会导致意外的结果或性能问题。

回答by Jon Gilbert

DETERMINISTIC results does not refer to different results sets being returned at different times (depending on what data has been added in the mean time). Moreover it is a reference to the result sets on different machines using the same data. If for example, you have 2 machines which run a function including uuid() or referencing server variables then these should be considered NOT DETERMINISTIC. This is useful for example in replication because the function calls are stored in the binary log (master) and then also executed by the slave. For details and examples see http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html

DETERMINISTIC 结果不是指在不同时间返回的不同结果集（取决于同时添加了哪些数据）。此外，它是对使用相同数据的不同机器上的结果集的引用。例如，如果您有 2 台机器运行一个函数，包括 uuid() 或引用服务器变量，那么这些应该被认为是不确定的。这在复制中很有用，因为函数调用存储在二进制日志（主）中，然后也由从执行。有关详细信息和示例，请参阅http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html

The use of DETERMINISTIC is thus (99% of the time) correct, not to be considered misuse.

因此，DETERMINISTIC 的使用（99% 的时间）是正确的，不会被视为误用。

回答by bikeman868

I think that your routine is deterministic. The documentation is not very clear and this has led to many people being very confused about this issue, which is actually more about replication than anything else.

我认为你的日常生活是确定性的。文档不是很清楚，这导致很多人对这个问题非常困惑，这实际上更多是关于复制而不是其他任何事情。

Consider a situation where you have replication set up between two databases. The master database keeps a log of all the stored routines that were executed including their input parameters, and ships this log to the the slave. The slave executes the same stored routines in the same order with the same input parameters. Will the slave database now contain identical data to the master database? If the stored routines create GUIDs and store these in the database then no, the master and slave databases will be different and replication will be broken.

考虑在两个数据库之间设置复制的情况。主数据库保存所有已执行的存储例程的日志，包括它们的输入参数，并将此日志发送到从属数据库。从站以相同的顺序使用相同的输入参数执行相同的存储例程。从数据库现在是否包含与主数据库相同的数据？如果存储的例程创建 GUID 并将它们存储在数据库中，那么不，主数据库和从数据库将不同，复制将被破坏。

The main purpose of the DETERMINISTIC flag is to tell MySQL whether including calls to this stored routine in the replication log will result in differences between the master database and the replicated slaves, and is therefore unsafe.

DETERMINISTIC 标志的主要目的是告诉 MySQL 在复制日志中包含对这个存储例程的调用是否会导致主数据库和复制从数据库之间的差异，因此是不安全的。

When deciding if the DETERMINISTIC flag is appropriate for a stored routine think of it like this: If I start with two identical databases and I execute my routine on both databases with the same input parameters will my databases still be identical? If they are then my routine is deterministic.

在决定 DETERMINISTIC 标志是否适用于存储例程时，可以这样考虑：如果我从两个相同的数据库开始，并使用相同的输入参数在两个数据库上执行我的例程，我的数据库是否仍然相同？如果它们是，那么我的例程是确定性的。

If you declare your routine is deterministic when it is not, then replicas of your main database might not be identical to the original because MySQL will only add the procedure call to the replication log, and executing the procedure on the slave does not produce identical results.

如果你声明你的例程不是确定性的，那么你的主数据库的副本可能与原始数据库不同，因为 MySQL 只会将过程调用添加到复制日志中，并且在从属上执行过程不会产生相同的结果.

If your routine is non-deterministic then MySQL must include the affected rows in the replication log instead. If you declare your routine as non-deterministic when it is not this will not break anything, but the replication log will contain all of the affected rows when just the procedure call would have been enough and this could impact performance.

如果您的例程是不确定的，那么 MySQL 必须在复制日志中包含受影响的行。如果你将你的例程声明为非确定性的，这不会破坏任何东西，但复制日志将包含所有受影响的行，只要过程调用就足够了，这可能会影响性能。

回答by John Watson

You're not missing anything. This function is non-deterministic. Declaring it deterministic won't cause your database to melt but it might affect performance. From the MySQL site: "Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices." But MySQL does not enforce or check if your declared deterministic routine is actually deterministic---MySQL trusts that you know what you are doing.

你没有错过任何东西。这个函数是不确定的。声明它确定性不会导致您的数据库崩溃，但可能会影响性能。来自 MySQL 站点：“将非确定性例程声明为 DETERMINISTIC 可能会导致优化器做出不正确的执行计划选择，从而导致意外结果。” 但是 MySQL 不会强制执行或检查您声明的确定性例程是否实际上是确定性的——MySQL 相信您知道自己在做什么。

回答by dooku

Deterministic is important if you have replication turned on or may use it one day. A non-deterministic function call that causes a row change (update or insert) for instance will need to be replicated using binary (row-based) where as a deterministic function can be replicated statement based. This becomes interesting when looking at your SQL examples above, which ones will happen the same (give the same result) when replicated using statement based, and which should be replicated using the result obtained in the master (row-based). If the statements are executed with the appropriate locking and can be guaranteed to execute in the same order on the Slave then they are indeed deterministic. If the locking / statement order that the Slave uses (no concurrency, serial processing of statements in the order they are started) means the answer can be different, then the function should be non-deterministic.

如果您打开了复制或有一天可能会使用它，则确定性很重要。例如，导致行更改（更新或插入）的非确定性函数调用将需要使用二进制（基于行）进行复制，而确定性函数可以基于语句进行复制。当查看上面的 SQL 示例时，这变得很有趣，当使用基于语句的复制时，哪些会发生相同的情况（给出相同的结果），哪些应该使用在 master 中获得的结果（基于行）进行复制。如果语句使用适当的锁定执行并且可以保证在 Slave 上以相同的顺序执行，那么它们确实是确定性的。如果 Slave 使用的锁定/语句顺序（无并发，