MySQL时间戳转日期函数详解 where查询格式化方法解析

<blockquote>MySQL中转换时间戳与日期需用FROM_UNIXTIME和UNIX_TIMESTAMP函数，WHERE查询时应将日期转为时间戳以利用索引，避免函数作用于字段导致全表扫描，提升性能。</blockquote> <p><img src=”https://img.php.cn/upload/article/001/503/042/175522098533165.jpeg” alt=”mysql时间戳转日期函数详解 where查询格式化方法解析”></p> <p>MySQL中将时间戳转换为日期，主要依靠<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM_UNIXTIME</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>函数，它能将Unix时间戳转化为可读的日期时间格式。在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句中进行时间戳的日期格式化查询时，关键在于确保比较的数据类型一致性，通常是将时间戳字段也转换为日期格式进行比较，或者更推荐的做法是将日期常量转换为时间戳，以利用索引提升查询效率。</p> <h3>解决方案</h3> <p>处理MySQL中的时间戳与日期转换，尤其是当我们需要在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句中基于日期进行筛选时，理解<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM_UNIXTIME()</pre><div class=”contentsignin”></div></div>函数是核心。这个函数接受一个Unix时间戳（即从’1970-01-01 00:00:00′ UTC到现在的秒数），并将其转换成一个可读的日期时间字符串或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATETIME</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>类型。</p> <p><strong>基本用法：</strong></p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT FROM_UNIXTIME(1678886400); — 结果可能是 ‘2023-03-15 00:00:00’ SELECT FROM_UNIXTIME(1678886400, ‘%Y-%m-%d %H:%i:%s’); — 结果 ‘2023-03-15 00:00:00′</pre><div class=”contentsignin”></div></div><p>第二个参数是格式字符串，和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATE_FORMAT()</pre><div class=”contentsignin”></div></div>函数用的格式符一样，这给了我们极大的灵活性。</p> <p><strong>在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句中的应用与考量：</strong></p> <p>当你的表里有一个<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>INT</pre><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BIGINT</pre><div class=”contentsignin”></div></div>类型的字段存储的是Unix时间戳，而你想根据某个日期范围来查询，直接的想法可能是：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 假设你的时间戳字段是 `created_at` SELECT * FROM your_table WHERE FROM_UNIXTIME(created_at, ‘%Y-%m-%d’) = ‘2023-03-15’;</pre><div class=”contentsignin”></div></div><p>这种写法确实能达到目的，但这里有个隐患，就是它对<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>字段应用了函数。如果<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>字段上有索引，那么这种写法会使得索引失效，导致全表扫描，这在数据量大时会成为性能瓶颈。我个人在处理这类查询时，总是会优先考虑如何让索引发挥作用。</p> <p>更推荐的做法是，将你要比较的日期常量转换成时间戳，然后直接与时间戳字段进行比较。这样<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>字段就能继续使用其上的索引。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 查询2023年3月15日当天的数据 SELECT * FROM your_table WHERE created_at >= UNIX_TIMESTAMP(‘2023-03-15 00:00:00’) AND created_at < UNIX_TIMESTAMP(‘2023-03-16 00:00:00’);</pre><div class=”contentsignin”></div></div><p>或者，如果你想查询某个时间点之前或之后的数据：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM your_table WHERE created_at < UNIX_TIMESTAMP(‘2023-03-15 12:00:00’);</pre><div class=”contentsignin”></div></div><p>这种方式，通过将查询条件“逆向”转换为时间戳，巧妙地避开了对表字段使用函数导致索引失效的问题。这在我看来，是处理时间戳查询时最重要的一点。</p> <h3>MySQL中时间戳与日期转换的常用函数有哪些？</h3> <p>在MySQL中，处理时间戳和日期时间类型之间的转换，远不止<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM_UNIXTIME</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>一个函数。实际上，它是一个函数族，各自扮演着不同的角色。理解这些函数能让你在数据处理时更加游刃有余。</p> <p>我们刚刚聊了<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM_UNIXTIME(unix_timestamp, [format])</pre><div class=”contentsignin”></div></div>，它负责把Unix时间戳转换为日期时间格式。它的兄弟函数是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>UNIX_TIMESTAMP([date])</pre><div class=”contentsignin”></div></div>。这个函数做的事情正好相反：它将一个日期时间表达式（可以是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATETIME</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATE</pre><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>TIMESTAMP</pre><div class=”contentsignin”></div></div>类型的值，或者一个日期时间字符串）转换为Unix时间戳。当你需要将一个人类可读的日期转换成数据库能直接比较的整数时间戳时，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>UNIX_TIMESTAMP()</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>就派上用场了。比如：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT UNIX_TIMESTAMP(‘2023-03-15 10:30:00’); — 结果：1678876200</pre><div class=”contentsignin”></div></div><p>除了这两个核心的转换函数，还有一些与日期时间格式化和解析紧密相关的函数也值得一提：</p> <ul> <li> <p><strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATE_FORMAT(date, format)</pre><div class=”contentsignin”></div></div></strong>: 这个函数用于将一个日期时间值格式化为指定格式的字符串。这在展示数据时非常有用，比如你只想显示日期部分，或者想以特定的顺序显示年月日。它和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM_UNIXTIME</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的第二个参数格式字符串是通用的。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT DATE_FORMAT(‘2023-03-15 10:30:00’, ‘%Y年%m月%d日 %H点%i分’); — 结果：’2023年03月15日 10点30分'</pre><div class=”contentsignin”></div></div></li> <li> <p><strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>STR_TO_DATE(str, format)</pre><div class=”contentsignin”></div></div></strong>: 这是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATE_FORMAT</pre><div class=”contentsignin”></div></div>的反向操作。它将一个字符串按照指定的格式解析成一个日期时间值。当你从外部系统获取到格式不一的日期字符串，需要将其导入到MySQL的日期时间字段时，这个函数就显得尤为重要。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT STR_TO_DATE(‘2023-03-15’, ‘%Y-%m-%d’); — 结果：’2023-03-15 00:00:00′</pre><div class=”contentsignin”></div></div></li> <li><p><strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>CONVERT_TZ(dt, from_tz, to_tz)</pre><div class=”contentsignin”></div></div></strong>: 虽然不是直接的时间戳转换，但涉及到时间处理，它能将一个日期时间值从一个时区转换到另一个时区。这对于处理跨时区的业务数据非常关键，尤其是在全球化应用中。</p></li> </ul> <p>理解并熟练运用这些函数，能让你在处理MySQL的时间数据时更加灵活和高效，避免许多常见的时间处理陷阱。</p> <h3>在WHERE子句中直接使用函数转换时间戳<a >为什么</a>可能影响查询性能？</h3> <p>这其实是一个非常经典的数据库性能优化问题，不单单针对MySQL的时间戳转换，而是所有在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句中对索引列进行函数操作的场景。当你对一个列应用了函数，比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM_UNIXTIME(created_at, ‘%Y-%m-%d’)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，数据库的查询优化器就无法直接利用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>列上可能存在的B-tree索引了。</p> <p>想象一下，数据库的索引就像一本书的目录。如果你要找“第十章”的内容，你可以直接翻到目录里对应的页码。但如果你的查询条件变成了“所有包含‘数据库优化’字样的章节”，那么目录就帮不上忙了，你不得不一页一页地翻遍整本书去查找。</p> <p>回到我们的时间戳例子，如果<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>字段被索引了，并且你执行的是：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM your_table WHERE created_at >= 1678886400 AND created_at < 1678972800;</pre><div class=”contentsignin”></div></div><p>数据库可以直接在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的索引树上进行范围查找，快速定位到符合条件的数据行。这个过程非常高效，尤其是在数据量巨大的表里。</p> <p>然而，当你写成这样：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM your_table WHERE FROM_UNIXTIME(created_at, ‘%Y-%m-%d’) = ‘2023-03-15’;</pre><div class=”contentsignin”></div></div><p>对于数据库来说，它无法预先知道<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM_UNIXTIME(created_at, ‘%Y-%m-%d’)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>会产生什么值。为了评估这个条件，它不得不对表中的每一行数据都执行<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FROM_UNIXTIME</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>函数，计算出结果，然后再与<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>’2023-03-15′</pre><div class=”contentsignin”></div></div>进行比较。这就意味着它需要扫描整个表，读取每一行数据，即使<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>字段有索引，这个索引也几乎形同虚设。这种操作被称为“全表扫描”（Full Table Scan），在生产环境中，尤其是在高并发或大数据量的场景下，全表扫描是性能杀手，会导致查询响应时间急剧增加，甚至拖垮整个数据库。</p> <p>所以，核心的优化思路是：<strong>让函数作用于常量值，而不是表中的列。</strong></p> <p>将<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>条件从<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>函数(列) = 值</pre><div class=”contentsignin”></div></div>转换为<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>列 运算符 函数(值)</pre><div class=”contentsignin”></div></div>。这样，列保持“干净”，索引就能被有效利用。这也就是为什么我之前会强调将日期常量转换为时间戳与时间戳字段进行比较的原因。</p> <h3>如何高效地在MySQL中查询特定日期范围的时间戳数据？</h3> <p>在MySQL中高效查询特定日期范围的时间戳数据，关键在于利用索引，并避免对时间戳字段本身应用函数。这里我会分享几种我常用的策略，它们都围绕着这个核心原则。</p> <p><strong>1. 使用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>UNIX_TIMESTAMP()</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>转换的日期范围</strong></p> <p>这是最常见且高效的方法。它通过将日期字符串转换为Unix时间戳，然后利用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>操作符来匹配时间戳范围。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 查询2023年3月15日全天的数据 SELECT * FROM your_table WHERE created_at BETWEEN UNIX_TIMESTAMP(‘2023-03-15 00:00:00’) AND UNIX_TIMESTAMP(‘2023-03-15 23:59:59’);</pre><div class=”contentsignin”></div></div><p>这种方式清晰明了，并且<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>字段上的索引能够得到充分利用。</p> <p><strong>2. 使用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>>=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>操作符精确匹配日期范围</strong></p> <p>有时，使用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>>=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>组合比<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>更灵活，尤其是在处理日期边界时，可以避免一些潜在的精度问题（尽管对于整秒的时间戳通常不是大问题）。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 查询2023年3月15日全天的数据 SELECT * FROM your_table WHERE created_at >= UNIX_TIMESTAMP(‘2023-03-15 00:00:00’) AND created_at < UNIX_TIMESTAMP(‘2023-03-16 00:00:00’);</pre><div class=”contentsignin”></div></div><p>这种方法同样能有效利用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>created_at</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>上的索引。我个人更倾向于这种写法，因为它更精确地定义了“当天”的范围，即从某天0点0分0秒开始，到第二天0点0分0秒之前。</p> <p><strong>3. 动态日期范围查询</strong></p> <p>在实际应用中，我们经常需要查询“最近7天”、“本月”等动态日期范围。这时，结合MySQL的日期函数来生成时间戳就显得非常方便。</p> <ul> <li> <p><strong>查询最近7天的数据（包括今天）：</strong></p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM your_table WHERE created_at >= UNIX_TIMESTAMP(CURDATE() – INTERVAL 7 DAY); — 如果需要精确到秒，可以这样： — WHERE created_at >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY))</pre><div class=”contentsignin”></div></div><p>这里<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>CURDATE()</pre><div class=”contentsignin”></div></div>获取当前日期（不含时间），<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>INTERVAL 7 DAY</pre><div class=”contentsignin”></div></div>是日期间隔。</p> </li> <li> <p><strong>查询本月的数据：</strong></p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM your_table WHERE created_at >= UNIX_TIMESTAMP(DATE_FORMAT(CURDATE(), ‘%Y-%m-01’)) AND created_at < UNIX_TIMESTAMP(DATE_ADD(DATE_FORMAT(CURDATE(), ‘%Y-%m-01’), INTERVAL 1 MONTH));</pre><div class=”contentsignin”></div></div><p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATE_FORMAT(CURDATE(), ‘%Y-%m-01’)</pre><div class=”contentsignin”></div></div>可以获取本月的第一天。</p> </li> </ul> <p>这些方法都遵循了不将函数应用于被索引列的原则，从而确保了查询的高效性。在设计数据库时，如果时间戳字段会被频繁地用于日期范围查询，务必为其加上索引。一个好的索引加上正确的查询姿势，能让你的数据库性能表现天壤之别。</p>