UNIX_TIMESTAMP工作原理及两段SQL执行差异的原因咨询

咱们先把UNIX_TIMESTAMP的工作逻辑说清楚：
在MySQL里，UNIX_TIMESTAMP()的核心作用就是把日期/时间值转换成从1970-01-01 00:00:00 UTC到目标时间的秒数，分两种使用场景：

• 不带参数时：直接返回当前系统时间的时间戳
• 带参数时：要求参数必须是有效的DATE/DATETIME/TIMESTAMP类型值，或者是能被MySQL自动转换成这些类型的字符串（比如包含完整日期+时间的字符串）

接下来咱们拆解你遇到的两个语句的差异：

为什么第一个语句能正常跑通？ #

你写的第一个语句：

select UNIX_TIMESTAMP(STR_TO_DATE("08:00:00","%H:%i:%s"));

STR_TO_DATE("08:00:00","%H:%i:%s")返回的是一个只有时间部分的结果，但在UNIX_TIMESTAMP()的参数上下文里，MySQL会自动帮你补全当前日期，把它转换成完整的DATETIME值（比如今天是2024-05-20，就会变成2024-05-20 08:00:00），所以UNIX_TIMESTAMP()能正常解析并生成对应的时间戳。

为什么第二个语句会"执行失败"？ #

你的第二个语句：

SET @date = STR_TO_DATE("08:00:00","%H:%i:%s"); 
select @date as _date, UNIX_TIMESTAMP(@date) as _unixtimestamp;

问题出在用户变量@date的类型上。在你用的MySQL 5.5版本里，当你把STR_TO_DATE返回的纯时间值赋值给用户变量时，这个变量会被存储成字符串类型（值就是"08:00:00"），而不是TIME或者DATETIME类型。

当UNIX_TIMESTAMP()拿到这个纯时间字符串时，MySQL 5.5没办法自动补全日期并把它转换成有效的日期时间值，自然就无法生成正确的时间戳，最终会返回NULL（这就是你感知到的"执行失败"）。

几个快速解决的方法 #

你可以用下面任意一种方式修复这个问题：

1. 赋值时直接补全日期，生成完整的DATETIME

SET @date = CONCAT(CURDATE(), ' ', STR_TO_DATE("08:00:00","%H:%i:%s"));
select @date as _date, UNIX_TIMESTAMP(@date) as _unixtimestamp;

这里用CURDATE()获取当前日期，和时间拼接成完整的日期时间字符串，赋值给变量后UNIX_TIMESTAMP()就能正常解析。

2. 调用UNIX_TIMESTAMP时强制转换变量类型

SET @date = STR_TO_DATE("08:00:00","%H:%i:%s");
select @date as _date, UNIX_TIMESTAMP(CAST(@date AS DATETIME)) as _unixtimestamp;

用CAST()把字符串类型的变量转换成DATETIME类型，MySQL会自动补全当前日期，从而生成正确的时间戳。

3. 让STR_TO_DATE直接生成完整的DATETIME值

SET @date = STR_TO_DATE(CONCAT(CURDATE(), ' 08:00:00'), '%Y-%m-%d %H:%i:%s');
select @date as _date, UNIX_TIMESTAMP(@date) as _unixtimestamp;

这种方式和第一种逻辑类似，直接让STR_TO_DATE生成带日期的完整时间值，变量类型会是日期时间类型，后续UNIX_TIMESTAMP()就能正常处理。

内容的提问来源于stack exchange，提问作者xyloweb