MySQL：年月日时分秒与年月日区间比较

最新推荐文章于 2021-12-09 17:02:03 发布

原创最新推荐文章于 2021-12-09 17:02:03 发布 · 4.7k 阅读

0 ·

CC 4.0 BY-SA版权

Java 同时被 2 个专栏收录

20 篇文章

订阅专栏

MySql

4 篇文章

订阅专栏

本文探讨了在MySQL数据库中使用不同日期字段进行查询时出现的结果差异，并通过具体SQL语句对比了START_DATE与APPLY_START_DATE两种场景下查询数量的不同。此外，还展示了如何在mybatis的mapper.xml文件中实现条件查询。

system_order 中有字段

APPLY_START_DATE 2018-03-06 00:00:00

START_DATE 2018-03-06

REFUSAL_REASON RF001

SELECT COUNT(*) FROM system_order

WHERE province = '澳门'
AND START_DATE >= '2018-03-06'
AND START_DATE <= '2018-03-06'

AND REFUSAL_REASON LIKE 'RF%'

结果为1

SELECT COUNT(*) FROM system_order
WHERE province = '澳门'
AND APPLY_START_DATE >= '2018-03-06'
AND APPLY_START_DATE <= '2018-03-06'

AND REFUSAL_REASON LIKE 'RF%'

结果为0

SELECT COUNT(*) FROM system_order
WHERE province = '澳门'
AND TO_DAYS(APPLY_START_DATE) >= TO_DAYS('2018-03-06')
AND TO_DAYS(APPLY_START_DATE) <= TO_DAYS('2018-03-06')
AND REFUSAL_REASON LIKE 'RF%'

结果为1

因为

SELECT TO_DAYS('2018-03-06')

等于 737124

SELECT TO_DAYS('2018-03-06 00:00:00') 也等于 737124

mybatis 的 mapper.xml中可以这样写

<select id="queryCountRiskMapByProvince" resultType="Integer">
select count(*) from system_order
<where>
<if test="province !=null and province.trim()!=''">and province = #{province}</if>
<if test="product_channel !=null and product_channel.trim()!=''">and product_channel = #{product_channel}</if>
<if test="start_date !=null and start_date.trim()!=''">and TO_DAYS(apply_start_date) <![CDATA[ >= ]]> TO_DAYS(#{start_date})</if>
<if test="end_date !=null and end_date.trim()!=''">and TO_DAYS(apply_start_date) <![CDATA[ <= ]]> TO_DAYS(#{end_date})</if>
</where>
</select>