MySQL日期时间datetime格式查询数据方式

布满荆棘的人生 2022-11-03 04:20 179阅读 0赞

msyql数据表时间字段为datetime时候，想要查询某一天数据

第一种

SELECT * from od WHERE create_time >= '2021-02-25 00:00:00' AND create_time <= '2021-02-25 23:59:59'  ;
    SELECT * from od WHERE create_time >= '2021-02-25' AND create_time < '2021-02-26'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70]  
第二种

select * from od where date(create_time)='2021-02-25';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 1]  
可以明显看到第一种查询效率要高的多  
explain一下

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 3]  
一个用到了索引，查询部分，一个全表扫描，没有用索引

between写法亦可

SELECT * from od WHERE create_time BETWEEN '2021-02-25 00:00:00' AND '2021-02-25 23:59:59'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 4]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 5]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70]: /images/20221024/852801cb3b0c4fb19fec33dfbc7eb879.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 1]: /images/20221024/84bc4c87af03439b9409874c33e58af5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 2]: /images/20221024/ca50e79d1bd74464b85cc2e22c0b87a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 3]: /images/20221024/5050ba5fe2b441858295ee7c8547d685.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 4]: /images/20221024/f65517c51b21411c90dbbeb58a55dea6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70 5]: /images/20221024/9bb0054225d840d49c7d2ae739a4ef73.png