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转换,所以效率比较高

Logo

更多推荐