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

最新推荐文章于 2024-04-26 15:53:57 发布

huey2672

最新推荐文章于 2024-04-26 15:53:57 发布

阅读量666

点赞数

分类专栏：数据库/MySQL 文章标签： MySQL

数据库/MySQL 专栏收录该内容

9 篇文章

订阅专栏

本文探讨了MySQL中SYSDATE()与NOW()函数的行为差异。SYSDATE()返回执行时刻的时间，不受SET TIMESTAMP影响，即使在同一语句中也可能返回不同值；NOW()则返回语句开始执行时的时间，并受SET TIMESTAMP影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.