MySQL函数：`CURDATE()`、`CURTIME()`与`NOW()`获取当前日期、时间和日期时间，并分析它们的性能差异。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL日期时间函数：CURDATE()、CURTIME()与NOW()的深度剖析

大家好，今天我们来深入探讨MySQL中用于获取当前日期、时间和日期时间的三个常用函数：CURDATE()、CURTIME()和NOW()。我们将剖析它们的定义、用法、返回值，更重要的是，分析它们在实际应用中的性能差异，以便大家能够根据具体需求选择最合适的函数，提升数据库查询效率。

1. 函数定义与基本用法

首先，让我们明确这三个函数的基本定义和用法：

CURDATE(): 返回当前日期，格式为 ‘YYYY-MM-DD’ 或 YYYYMMDD，具体取决于上下文（字符串或数值）。
CURTIME(): 返回当前时间，格式为 ‘HH:MM:SS’ 或 HHMMSS，同样取决于上下文。
NOW(): 返回当前日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’ 或 YYYYMMDDHHMMSS，取决于上下文。

下面是一些简单的示例：

SELECT CURDATE(); -- 输出: 例如 '2023-10-27'
SELECT CURTIME(); -- 输出: 例如 '15:30:00'
SELECT NOW();     -- 输出: 例如 '2023-10-27 15:30:00'

这三个函数都不需要任何参数。它们直接从MySQL服务器获取当前系统时间。

2. 返回值类型与格式

CURDATE()、CURTIME()和NOW()的返回值类型取决于上下文。如果在字符串上下文中（例如，与其他字符串连接），它们将返回字符串类型；如果在数值上下文中（例如，用于算术运算），它们将返回数值类型。

SELECT CURDATE() + 0;   -- 数值上下文，返回 YYYYMMDD 格式的数值
SELECT CURTIME() + 0;   -- 数值上下文，返回 HHMMSS 格式的数值
SELECT NOW() + 0;       -- 数值上下文，返回 YYYYMMDDHHMMSS 格式的数值

SELECT CONCAT('Today is ', CURDATE());  -- 字符串上下文，返回 'Today is 2023-10-27'

理解返回值类型对于正确使用这些函数至关重要，特别是当需要进行数据类型转换或者与其他数据进行比较时。

3. 精确度问题

NOW()函数返回日期和时间，它的精确度取决于MySQL服务器的配置。默认情况下，NOW()的精度为秒。如果需要更高的精度，例如毫秒或微秒，可以使用SYSDATE()函数或者在MySQL 5.6.4及更高版本中使用NOW(fsp)，其中fsp是从0到6的精度值，表示小数秒的位数。

SELECT NOW(3);  -- 输出: 例如 '2023-10-27 15:30:00.123'
SELECT SYSDATE(6); -- 输出: 例如 '2023-10-27 15:30:00.123456'

需要注意的是，SYSDATE()和NOW()在某些情况下表现不同，后面我们会详细讨论。CURDATE()和CURTIME()没有精度问题，因为它们分别只返回日期和时间，不涉及小数秒。

4. `NOW()` vs `SYSDATE()` 的区别

NOW()和SYSDATE()都返回当前日期和时间，但它们之间存在一个重要的区别：

NOW(): 在查询开始执行时确定一次时间，并在整个查询执行过程中保持不变。
SYSDATE(): 每次调用时都会重新计算时间。

这个区别在存储过程、触发器或者复杂查询中尤为重要。考虑以下例子：

-- 创建测试表
CREATE TABLE test_time (
    id INT PRIMARY KEY AUTO_INCREMENT,
    time1 DATETIME,
    time2 DATETIME
);

-- 存储过程示例
DELIMITER //
CREATE PROCEDURE insert_time()
BEGIN
    INSERT INTO test_time (time1, time2) VALUES (NOW(), SYSDATE());
    SELECT SLEEP(5);  -- 暂停5秒
    INSERT INTO test_time (time1, time2) VALUES (NOW(), SYSDATE());
END //
DELIMITER ;

-- 调用存储过程
CALL insert_time();

-- 查询结果
SELECT * FROM test_time;

-- 结果示例:
-- id | time1               | time2
-- -- | ------------------- | -------------------
-- 1  | 2023-10-27 15:30:00 | 2023-10-27 15:30:00
-- 2  | 2023-10-27 15:30:00 | 2023-10-27 15:30:05

在这个例子中，NOW()在存储过程开始时确定一次时间，因此两次插入的time1值相同。而SYSDATE()每次调用都会重新计算时间，因此两次插入的time2值相差5秒。

总结：

函数描述

NOW() 返回查询开始执行时的日期和时间。在整个查询执行过程中，即使查询花费较长时间，NOW()的值也保持不变。

SYSDATE() 返回每次调用时的日期和时间。即使在同一个查询中多次调用SYSDATE()，每次调用的值都可能不同，因为它会实时获取系统时间。SYSDATE()在需要获取语句执行的精确时间点时非常有用，例如在审计日志或性能分析中。需要注意的是，SYSDATE()可能会对性能产生一定的影响，因为它每次都会调用系统时间。

函数	描述
`NOW()`	返回查询开始执行时的日期和时间。在整个查询执行过程中，即使查询花费较长时间，`NOW()`的值也保持不变。
`SYSDATE()`	返回每次调用时的日期和时间。即使在同一个查询中多次调用`SYSDATE()`，每次调用的值都可能不同，因为它会实时获取系统时间。`SYSDATE()`在需要获取语句执行的精确时间点时非常有用，例如在审计日志或性能分析中。需要注意的是，`SYSDATE()`可能会对性能产生一定的影响，因为它每次都会调用系统时间。

选择NOW()还是SYSDATE()取决于你的具体需求。如果需要在整个查询过程中保持时间一致，使用NOW()。如果需要获取每次调用时的实时时间，使用SYSDATE()。

5. 性能分析

CURDATE()、CURTIME()和NOW()都是内置函数，它们的执行速度通常非常快。但是，在某些情况下，它们的性能差异可能会变得明显。

简单查询： 在简单的SELECT查询中，这三个函数的性能差异可以忽略不计。 MySQL查询优化器可以有效地处理这些函数，并将其优化为常量。
复杂查询： 在复杂的查询中，例如包含大量连接、子查询或者排序操作的查询，NOW()的性能可能会略优于SYSDATE()。这是因为NOW()只计算一次时间，而SYSDATE()每次调用都会重新计算时间，这会增加CPU的负担。
大数据量更新： 在大数据量的INSERT或UPDATE操作中，如果需要使用当前日期和时间，NOW()的性能优势会更加明显。因为NOW()只需要计算一次时间，然后将其用于所有记录的更新，而SYSDATE()需要为每个记录重新计算时间。

为了更直观地了解它们的性能差异，我们可以进行一些简单的性能测试。例如，我们可以创建一个包含大量记录的表，并使用NOW()和SYSDATE()分别更新这些记录，然后比较它们的执行时间。

-- 创建测试表
CREATE TABLE test_performance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(255),
    time1 DATETIME,
    time2 DATETIME
);

-- 插入大量数据
INSERT INTO test_performance (data) SELECT REPEAT('a', 200) FROM numbers LIMIT 100000;

-- 更新数据，使用 NOW()
SET @start_time = NOW(6);
UPDATE test_performance SET time1 = NOW(6);
SET @end_time = NOW(6);

SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS duration_now;

-- 更新数据，使用 SYSDATE()
SET @start_time = NOW(6);
UPDATE test_performance SET time2 = SYSDATE(6);
SET @end_time = NOW(6);

SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS duration_sysdate;

（注意：上面的numbers表需要事先存在，可以通过创建一个包含连续数字的表来实现，例如 CREATE TABLE numbers (n INT); INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); INSERT INTO numbers SELECT n + 10 FROM numbers; ... 插入足够多的数据）

通过运行这些测试，我们可以获得NOW()和SYSDATE()在不同场景下的性能数据，从而更好地了解它们的性能差异。

总结：

在大多数情况下，CURDATE()、CURTIME()和NOW()的性能差异可以忽略不计。
在复杂的查询或者大数据量的更新操作中，NOW()的性能可能会略优于SYSDATE()。
如果对性能要求非常高，可以考虑使用NOW()并将其存储在一个变量中，然后在查询中使用该变量，以避免重复计算时间。

6. 索引与优化

这些日期时间函数本身无法直接使用索引来优化查询，因为它们是运行时函数，每次执行都会返回不同的值。但是，可以通过以下方式来间接利用索引：

预计算： 如果查询需要根据当前日期或时间进行过滤，可以考虑将当前日期或时间预先计算出来，并将其存储在一个变量中，然后在查询中使用该变量。这样可以避免在每次查询时都重新计算日期或时间，从而提高查询效率。
范围查询： 如果查询需要根据日期或时间范围进行过滤，可以使用BETWEEN运算符或者比较运算符（<、>、<=、>=）来指定日期或时间范围，并确保相关字段上存在索引。例如:

SELECT * FROM orders WHERE order_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE(); -- order_date 字段上存在索引

分区表： 如果表中的数据量非常大，可以考虑使用分区表，并根据日期或时间进行分区。这样可以有效地减少查询需要扫描的数据量，从而提高查询效率。

7. 时区问题

CURDATE()、CURTIME()和NOW()返回的日期和时间是MySQL服务器的当前时区。如果应用程序需要使用不同的时区，可以使用CONVERT_TZ()函数进行时区转换。

SELECT NOW(), CONVERT_TZ(NOW(), '+00:00', '+08:00'); -- 将 UTC 时间转换为东八区时间

确保数据库服务器的时区设置正确，并且应用程序能够正确处理时区转换，对于保证数据的准确性和一致性非常重要。

8. 其他相关函数

除了CURDATE()、CURTIME()和NOW()之外，MySQL还提供了许多其他有用的日期和时间函数，例如：

DATE(): 提取日期部分。
TIME(): 提取时间部分。
YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND(): 提取日期或时间的各个部分。
DATE_ADD()、DATE_SUB(): 在日期或时间上增加或减少一个时间间隔。
DATEDIFF()、TIMEDIFF(): 计算两个日期或时间之间的差值。
DATE_FORMAT(): 将日期或时间格式化为指定的字符串。

熟练掌握这些函数可以更灵活地处理日期和时间数据，满足各种复杂的业务需求。

使用场景以及最佳实践总结

CURDATE()和CURTIME()：用于获取独立的日期和时间信息，常用于记录事件发生的时间戳，或者在创建报表时按日期或时间进行分组。
NOW()：提供完整的日期时间信息，适用于需要同时记录日期和时间的场景，例如订单创建时间、用户注册时间等。
SYSDATE()：当需要审计操作的精确时间点，或者在存储过程中需要多次记录不同时刻的时间时，SYSDATE()更为合适。

最佳实践：

根据实际需求选择合适的函数，避免不必要的性能损耗。
在大型数据更新时，优先使用NOW()，并在需要时结合变量。
确保数据库和应用程序的时区设置一致，避免时区问题导致的数据错误。
合理使用索引和分区表优化查询性能。
熟练掌握常用的日期和时间函数，灵活处理各种业务需求。

希望今天的讲解能够帮助大家更好地理解和使用CURDATE()、CURTIME()和NOW()这三个常用的MySQL日期时间函数。在实际应用中，根据具体的业务场景和性能要求，选择最合适的函数，并结合索引、分区表等优化技术，可以有效地提高数据库查询效率，提升应用程序的性能。