MySQL中NOW()和SYSDATE()的区别

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 function or trigger, NOW() returns the time at which the function or triggering statement began to execute.)

mysql> SELECT NOW(), SLEEP(2), NOW();
+-----------------------------+----------------+------------------------------+
| NOW()                           | SLEEP(2)    | NOW()                           |
+-----------------------------+----------------+------------------------------+
| 2014-10-27 15:31:16   |        0           | 2014-10-27 15:31:16   |
+-----------------------------+----------------+------------------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+-----------------------------+----------------+------------------------------+
| SYSDATE()                   | SLEEP(2)   | SYSDATE()                    |
+-----------------------------+----------------+------------------------------+
| 2014-10-27 15:31:50  |        0            | 2014-10-27 15:31:52   |
+-----------------------------+----------------+------------------------------+

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 nondeterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging.

Alternatively, you can use the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(). This works if the option is used on both the master and the slave.

The nondeterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it.