MySQL高级函数之：`MAKETIME()`：其在创建时间时的应用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 高级函数之 MAKETIME()：精雕细琢你的时间

大家好，今天我们来深入探讨 MySQL 中一个鲜为人知但功能强大的时间函数：MAKETIME()。很多人可能对 DATE()、TIME()、DATETIME() 这些函数非常熟悉，但 MAKETIME() 却常常被忽视。然而，在某些特定场景下，它能提供更为精确和高效的时间创建方式。让我们一起揭开 MAKETIME() 的神秘面纱，学会如何在实际开发中灵活运用它。

1. `MAKETIME()` 函数的基本语法

MAKETIME() 函数的语法非常简洁：

MAKETIME(hour, minute, second)

它接受三个整数参数：

hour: 小时 (0-23)
minute: 分钟 (0-59)
second: 秒 (0-59)

函数会根据这三个参数，返回一个 TIME 类型的值。如果任何一个参数超出其有效范围，MySQL 会进行相应的调整，使其落在有效范围内。

2. `MAKETIME()` 的返回值类型

MAKETIME() 函数始终返回一个 TIME 类型的值。 TIME 类型在 MySQL 中用于表示一天中的时间，格式为 HH:MM:SS。

3. 简单示例：创建时间

让我们从一些简单的例子开始，了解 MAKETIME() 的基本用法：

SELECT MAKETIME(10, 30, 45);  -- 返回 '10:30:45'

SELECT MAKETIME(0, 0, 0);   -- 返回 '00:00:00'

SELECT MAKETIME(23, 59, 59);  -- 返回 '23:59:59'

这些例子展示了如何使用 MAKETIME() 创建基本的时间值。接下来，我们将探讨一些更高级的用法，以及在实际应用中可能遇到的问题。

4. 参数超出范围的处理

MAKETIME() 函数对超出范围的参数有自己的处理方式。它会将超出范围的值进行调整，使其落在有效范围内。例如：

SELECT MAKETIME(25, 10, 20);  -- 返回 '01:10:20' (25 被调整为 1)

SELECT MAKETIME(10, 70, 30);  -- 返回 '11:10:30' (70 分钟相当于 1 小时 10 分钟)

SELECT MAKETIME(10, 30, 80);  -- 返回 '10:31:20' (80 秒相当于 1 分钟 20 秒)

可以看到，MAKETIME() 会自动将超出范围的分钟和秒数转换成对应的小时、分钟和秒。小时数如果超过 23，则会从 0 开始循环。

5. 与其他日期和时间函数的结合使用

MAKETIME() 可以与其他日期和时间函数结合使用，创建更复杂的日期和时间值。例如，我们可以使用 CURDATE() 函数获取当前日期，然后使用 MAKETIME() 创建一个指定时间的 DATETIME 值：

SELECT CONCAT(CURDATE(), ' ', MAKETIME(15, 0, 0));  -- 返回类似 '2023-10-27 15:00:00' 的 DATETIME 值

这里，CONCAT() 函数将当前日期（CURDATE() 的返回值）和一个由 MAKETIME() 创建的时间字符串连接起来，生成一个 DATETIME 字符串。需要注意的是，这个结果仍然是一个字符串，而不是 DATETIME 类型。要将其转换为 DATETIME 类型，可以使用 STR_TO_DATE() 函数：

SELECT STR_TO_DATE(CONCAT(CURDATE(), ' ', MAKETIME(15, 0, 0)), '%Y-%m-%d %H:%i:%s');  -- 返回 DATETIME 类型的值

6. 在存储过程中使用 `MAKETIME()`

MAKETIME() 同样可以在存储过程中使用。这使得我们可以在存储过程中动态地创建时间值。例如，我们可以创建一个存储过程，根据传入的小时、分钟和秒，创建一个新的 TIME 值，并将其插入到数据库中：

DELIMITER //
CREATE PROCEDURE InsertTime(IN hour INT, IN minute INT, IN second INT)
BEGIN
  INSERT INTO my_table (time_column) VALUES (MAKETIME(hour, minute, second));
END //
DELIMITER ;

-- 调用存储过程
CALL InsertTime(8, 30, 0);

在这个例子中，InsertTime 存储过程接受三个输入参数：hour, minute, second。它使用 MAKETIME() 函数创建一个 TIME 值，并将其插入到 my_table 表的 time_column 列中。

7. 应用场景：定时任务调度

MAKETIME() 在定时任务调度方面可以发挥作用。假设我们需要在一个数据库中存储每天需要执行的任务，并且记录任务执行的时间。我们可以使用 MAKETIME() 来简化时间的设置。

例如，我们有一个 tasks 表，包含以下字段：

id: 任务 ID (INT, PRIMARY KEY)
task_name: 任务名称 (VARCHAR)
execution_hour: 执行小时 (INT)
execution_minute: 执行分钟 (INT)
execution_second: 执行秒 (INT)
last_execution_time: 上次执行时间 (DATETIME)

我们可以创建一个存储过程，用于检查是否有需要执行的任务，并更新 last_execution_time：

DELIMITER //
CREATE PROCEDURE CheckAndExecuteTasks()
BEGIN
  DECLARE current_time TIME;
  SET current_time = CURTIME();

  UPDATE tasks
  SET last_execution_time = NOW()
  WHERE execution_hour = HOUR(current_time)
    AND execution_minute = MINUTE(current_time)
    AND execution_second = SECOND(current_time)
    AND last_execution_time IS NULL; -- 只执行一次
END //
DELIMITER ;

--  创建一个事件定时执行存储过程
CREATE EVENT IF NOT EXISTS `event_check_and_execute_tasks`
ON SCHEDULE EVERY 1 SECOND
STARTS (CURRENT_TIMESTAMP)
DO CALL `CheckAndExecuteTasks`();

或者更灵活的方式，使用MAKETIME() 函数：

DELIMITER //
CREATE PROCEDURE CheckAndExecuteTasks()
BEGIN
  DECLARE current_time TIME;
  DECLARE task_time TIME;

  SET current_time = CURTIME();

  UPDATE tasks
  SET last_execution_time = NOW()
  WHERE MAKETIME(execution_hour, execution_minute, execution_second) = current_time
    AND last_execution_time IS NULL; -- 只执行一次
END //
DELIMITER ;

这个存储过程首先获取当前时间 CURTIME()，然后使用 MAKETIME() 函数将 execution_hour, execution_minute, execution_second 转换为 TIME 类型，与当前时间进行比较。如果匹配，则更新 last_execution_time。

8. 与 `TIMESTAMP` 和 `DATETIME` 的比较

TIMESTAMP 和 DATETIME 类型都用于存储日期和时间，但它们之间存在一些重要的区别：

特性	TIMESTAMP	DATETIME
存储空间	4 字节	8 字节
范围	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
时区转换	自动进行时区转换	不进行时区转换
可移植性	依赖于时区设置，移植性较差	移植性较好

TIMESTAMP 类型在存储时会转换为 UTC 时间，并在检索时转换回当前时区。 DATETIME 类型则直接存储日期和时间，不进行时区转换。因此，在选择使用 TIMESTAMP 还是 DATETIME 时，需要根据具体的应用场景进行权衡。如果需要存储与时区相关的时间信息，并且需要自动进行时区转换，那么 TIMESTAMP 是一个不错的选择。如果需要存储绝对的日期和时间，并且不需要进行时区转换，那么 DATETIME 更加合适。

MAKETIME() 函数创建的是 TIME 类型，只包含时间信息，不包含日期信息。因此，它与 TIMESTAMP 和 DATETIME 的用途不同，主要用于表示一天中的某个特定时间。

9. 性能考量

在使用 MAKETIME() 函数时，也需要考虑性能问题。虽然 MAKETIME() 函数本身执行速度很快，但在大量数据处理时，频繁调用 MAKETIME() 可能会对性能产生一定的影响。特别是在 WHERE 子句中使用 MAKETIME() 进行条件判断时，可能会导致索引失效，从而降低查询速度。

为了提高性能，可以考虑以下几点：

尽量避免在 WHERE 子句中直接使用 MAKETIME() 函数。可以将 MAKETIME() 的计算结果预先存储在一个单独的列中，然后在 WHERE 子句中使用该列进行条件判断。
如果必须在 WHERE 子句中使用 MAKETIME() 函数，可以考虑创建函数索引 (Function-Based Index)。函数索引允许我们对函数的计算结果创建索引，从而提高查询速度。但是需要注意，函数索引在 MySQL 5.7 及更早版本中只支持虚拟列 (Virtual Column)，而在 MySQL 8.0 及更高版本中才支持真正的函数索引。
在存储过程中使用 MAKETIME() 函数时，尽量减少对 MAKETIME() 的调用次数。可以将 MAKETIME() 的计算结果缓存在一个局部变量中，然后在存储过程的其他地方使用该变量。

10. 总结：灵活运用 MAKETIME() 函数

MAKETIME() 函数是一个简单而强大的时间函数，可以帮助我们创建和处理 TIME 类型的值。通过与其他日期和时间函数的结合使用，我们可以实现更复杂的日期和时间操作。在实际开发中，我们需要根据具体的应用场景，灵活运用 MAKETIME() 函数，才能充分发挥它的优势。希望今天的讲解能够帮助大家更好地理解和使用 MAKETIME() 函数。

MySQL 高级函数之 MAKETIME()：精雕细琢你的时间

1. MAKETIME() 函数的基本语法

2. MAKETIME() 的返回值类型