Thursday, December 03, 2009


NOW() 函数,因为在二进制日志里已经包括了时间戳,可以被正确复制到slave server上。
UUID() 函数,具有非确定性,所以不能被复制到slave server,所以在存储过程或者触发器中要慎用。
the UUID() function is nondeterministic (and does not replicate). You should be careful about using such functions in triggers. It is not safe.
SYSDATE() 函数也具有非确定性,与NOW()函数不一样,在同步复制时会与master上的时间不一致。官方文档说明如下:
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

As of MySQL 5.0.13, SYSDATE() returns the time at which it executes. This differs from the behavior for NOW(), which returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.)

mysql> SELECT NOW(), SLEEP(2), NOW();
| NOW() | SLEEP(2) | NOW() |
| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |

| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |

In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP, it is non-deterministic and therefore unsafe for replication. If that is a problem, you can start the server with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(). The non-deterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it.

No comments :