mysql 时间类型查询条件写法

昨天在写一个sql查询3个月前的历史数据时，直接在时间字段上用“<2010-07-09”来过滤，结果sql执行一直报warning：Incorrect date value: '1988' for column 'gmt_create' at row 1


今天再次做了验证，发现如下：
表结构
root@test 02:38:59>show create table xc\G
*************************** 1. row ***************************
       Table: xc
Create Table: CREATE TABLE `xc` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `gmt_create` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)


数据
root@test 02:51:13>select * from xc;
+----+------------+
| id | gmt_create |
+----+------------+
|  1 | 2010-10-05 |
|  2 | 2010-10-06 |
|  3 | 2010-09-25 |
+----+------------+
3 rows in set (0.00 sec)


//不做任何处理的普通写法
大于查询
root@test 02:53:32>select * from xc where  gmt_create>2010-10-12;
+----+------------+
| id | gmt_create |
+----+------------+
|  1 | 2010-10-05 |
|  2 | 2010-10-06 |
|  3 | 2010-09-25 |
+----+------------+
3 rows in set, 1 warning (0.00 sec)


root@test 02:53:38>show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect date value: '1988' for column 'gmt_create' at row 1
1 row in set (0.00 sec)


//存在未知的隐式转换，查询结果出错并告警


小于查询
root@test 02:53:44>select * from xc where  gmt_create<2010-10-12;
Empty set, 1 warning (0.00 sec)


root@test 02:54:52>show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect date value: '1988' for column 'gmt_create' at row 1
1 row in set (0.00 sec)


//同样的未知隐式转换，查询结果出错




添加“‘’”号将查询日期作为一个整体传入
大于查询
root@test 02:54:57>select * from xc where  gmt_create>'2010-10-12';
Empty set (0.00 sec)


小于查询
root@test 02:56:48>select * from xc where  gmt_create<'2010-10-12';
+----+------------+
| id | gmt_create |
+----+------------+
|  1 | 2010-10-05 |
|  2 | 2010-10-06 |
|  3 | 2010-09-25 |
+----+------------+
3 rows in set (0.00 sec)


//此时转换正确且查询结果OK，亦无告警


mysql内部对于日期的书写比较随意，所以在平时写sql的时候要注意尽量规范。使用了“''”把日期当做一个整体的字符来转换时可以转换正确，不加则可能转换出错返回错误结果。
另外也可以通过mysql自带的函数将其进行转换：
root@test 03:25:24>select * from xc where  gmt_create<str_to_date('2010-10-12','%Y-%m-%d');
+----+------------+
| id | gmt_create |
+----+------------+
|  1 | 2010-10-05 |
|  2 | 2010-10-06 |
|  3 | 2010-09-25 |
+----+------------+
3 rows in set (0.00 sec)


root@test 03:25:43>select * from xc where  gmt_create>str_to_date('2010-10-12','%Y-%m-%d');
Empty set (0.00 sec)


此外，不做处理的普通写法还有可能走错索引！！


结论：关于日期查询的时候在写where条件的时候一定要注意
1）用括号将其封装，让mysql将其作为一个整体字符串内部转换；
2）善用mysql自带的函数str_to_date事先转换


这个以后在sql审核的时候一定要多加注意下，尽量规范化才行！