mysql中date与datetime在数据统计中的区别

最新推荐文章于 2025-08-09 18:20:04 发布

dataee

最新推荐文章于 2025-08-09 18:20:04 发布

阅读量137

点赞数

文章标签：数据库

本文深入探讨了MySQL中日期比较及筛选语句的工作原理，通过具体实例展示了如何正确使用日期参数进行数据检索。

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

Type：mysql

表结构：

 CREATE TABLE `abc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM ;

数据：

mysql> select * from abc;
+----+-------------+
| id | create_date |
+----+-------------+
|  1 | 2011-08-12  |
|  2 | 2011-08-12  |
|  3 | 2011-08-12  |
|  4 | 2011-08-12  |
|  5 | 2011-08-12  |
|  6 | 2011-08-12  |
|  7 | 2011-08-12  |
|  8 | 2011-08-12  |
|  9 | 2011-08-11  |
| 10 | 2011-08-11  |
| 11 | 2011-08-11  |
| 12 | 2011-08-11  |
| 13 | 2011-08-11  |
| 14 | 2011-08-11  |
+----+-------------+

执行sql1：

mysql> select * from abc where create_date>='2011-08-11 00:00:00';
+----+-------------+
| id | create_date |
+----+-------------+
|  1 | 2011-08-12  |
|  2 | 2011-08-12  |
|  3 | 2011-08-12  |
|  4 | 2011-08-12  |
|  5 | 2011-08-12  |
|  6 | 2011-08-12  |
|  7 | 2011-08-12  |
|  8 | 2011-08-12  |
|  9 | 2011-08-11  |
| 10 | 2011-08-11  |
| 11 | 2011-08-11  |
| 12 | 2011-08-11  |
| 13 | 2011-08-11  |
| 14 | 2011-08-11  |
+----+-------------+
14 rows in set (0.00 sec)

执行sql2：

mysql> select * from abc where create_date>='2011-08-11 00:00:01';
+----+-------------+
| id | create_date |
+----+-------------+
|  1 | 2011-08-12  |
|  2 | 2011-08-12  |
|  3 | 2011-08-12  |
|  4 | 2011-08-12  |
|  5 | 2011-08-12  |
|  6 | 2011-08-12  |
|  7 | 2011-08-12  |
|  8 | 2011-08-12  |
+----+-------------+
8 rows in set (0.00 sec)

执行sql3：

mysql> select * from abc where create_date='2011-08-11 00:00:00';
+----+-------------+
| id | create_date |
+----+-------------+
|  9 | 2011-08-11  |
| 10 | 2011-08-11  |
| 11 | 2011-08-11  |
| 12 | 2011-08-11  |
| 13 | 2011-08-11  |
| 14 | 2011-08-11  |
+----+-------------+
6 rows in set (0.01 sec)

结论：

在mysql下date 如：2011-08-11 默认为'2011-08-11 00:00:00'，可以返回正确的结果集