mysql timestamp 和 date比较
1.问题: 列名为create_time代表我新数据的生成时间,如果想提取某一天的数据,该如何比较注意:create_time为timestamp类型,mysql允许自动生成这个时间,所以表中有这一列为了记录数据的产生时间。需要提取某一天的数据,例如这一天为:2016-07-29,如何比较?一种方案:timestamp转为date直接比较“` DATE(expr)Ex
·
1.问题: 列名为create_time代表我新数据的生成时间,如果想提取某一天的数据,该如何比较
注意:create_time为timestamp类型,mysql允许自动生成这个时间,所以表中有这一列为了记录数据的产生时间。
需要提取某一天的数据,例如这一天为:2016-07-29,如何比较?
一种方案:timestamp转为date直接比较
“`
DATE(expr)
Extracts the date part of the date or datetime expression *expr
*.
mysql> **SELECT DATE(‘2003-12-31 01:02:03’);
** -> ‘2003-12-31’
官网给出的解释如上:
测试:
```sql
MariaDB [prf]> create table time_test(id int ,time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
MariaDB [prf]> insert into time_test values(1,null);
Query OK, 1 row affected (0.00 sec)
MariaDB [prf]> select * from time_test;
+------+---------------------+
| id | time |
+------+---------------------+
| 1 | 2016-07-29 21:07:38 |
+------+---------------------+
1 row in set (0.00 sec)
MariaDB [prf]> select * from time_test where date(time) ='2016-07-29';
+------+---------------------+
| id | time |
+------+---------------------+
| 1 | 2016-07-29 21:07:38 |
+------+---------------------+
1 row in set (0.01 sec)
MariaDB [prf]> select * from time_test;
+------+---------------------+
| id | time |
+------+---------------------+
| 1 | 2016-07-29 21:07:38 |
| 2 | 2016-07-28 14:23:22 |
| 2 | 2016-07-27 14:23:22 |
| 2 | 2016-07-26 14:23:22 |
+------+---------------------+
4 rows in set (0.00 sec)
MariaDB [prf]> create index time_index on time_test(time);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [prf]> select * from time_test where date(time)='2016-07-28';
+------+---------------------+
| id | time |
+------+---------------------+
| 2 | 2016-07-28 14:23:22 |
+------+---------------------+
1 row in set (0.00 sec)
MariaDB [prf]> explain select * from time_test where date(time)='2016-07-28';
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | time_test | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
以上有一个问题,虽然能够查询,但是如果我对time这一列进行了索引,由于用了函数,所以这一列查询无法用到索引,依然是全表扫描如何解决这个问题呢???
MariaDB [prf]> select * from time_test where time between '2016-07-28' and '2016-07-29';
+------+---------------------+
| id | time |
+------+---------------------+
| 2 | 2016-07-28 14:23:22 |
+------+---------------------+
1 row in set (0.00 sec)
MariaDB [prf]> explain select * from time_test where time between '2016-07-28' and '2016-07-29';
+------+-------------+-----------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------+------------+---------+------+------+-----------------------+
| 1 | SIMPLE | time_test | range | time_index | time_index | 4 | NULL | 1 | Using index condition |
+------+-------------+-----------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
以上采用了between and 或者 >= <=都可以做到走索引,而且不用每一行time值都进行date转换,所以效率比较高
更多推荐
已为社区贡献1条内容
所有评论(0)