sqlalchemy查询数据为空，查询范围对应的数据在数据库真实存在

记录一个开发过程遇到的小bug，构造些伪数据还原并解释。

"""
场景：传参触发了查询条件，数据库中是存在传参对应范围的数据，但是通过查询条件得到的查询结果为空
"""
入参场景一：
start_time = "2023-11-13"
end_time = "2023-11-13"
入参场景二：
start_time = "2023-11-10"
end_time = "2023-11-15"

表字段time 类型定义：
time：datetime
表字段time下的值为：
2023-11-13 00:00:00
2023-11-13 11:00:00
...
查询条件：
query = session.query(model).filter(model.time >= start_time, alarm_model.time <= end_time, model.name.in_(name_list))
使用入参场景一的参数值查询得到的结果就是空
使用入参场景二的参数值就可以查询到2023-11-13对应的值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

原因分析说明：

time 字段类型和入参参数类型不同，其中 filter(model.time >= start_time, alarm_model.time <= end_time) 这里的条件，转换成SQL就是
WHERE time >= ‘2023-11-13’ AND time <= ‘2023-11-13’。
这样就是在和 time这个时间字段做直接字符串比较，所以 ‘2023-11-13’ 不能匹配 '2023-11-13 16:02:36’格式的时间。

解决方案，找两种分析

方案一：使用完整的时间字符串，而不是只用日期，这样会形成一个时间范围，就能和time的时间格式匹配

WHERE time >= '2023-11-13 00:00:00' AND time <= '2023-11-13 23:59:59'
1

但是这个方案有一个弊端，需要数据库扫描整个时间范围内的time值，如果这一天的数据量很大，查询会比较慢。

方案二：使用日期函数提取日期部分，只需要对比日期部分，跳过时间部分的比较利用索引，查询效率会更高。

import sqlalchemy as sa
或者
from sqlalchemy import func

query = session.query(model).filter(
    sa.func.date(model.time) == start_date,model.time <= end_time, model.name.in_(name_list))
)
或者
query = session.query(model).filter(
    func.date(model.time) == start_date,model.time <= end_time, model.name.in_(name_list))
)
区别主要在于:

方式1 通过sqlalchemy模块名作为前缀,定义别名,访问func子模块，避免与当前命名空间冲突
方式2 直接导入func到当前命名空间，直接使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

从性能的角度来说，
1，完整时间范围查询会全表扫描指定日期内所有记录, IO和计算量都很大。
2，使用日期函数过滤可以省去时间部分的比对,可以利用索引,查询效率会更高。
具体问题看表和索引情况，但正常情况使用日期函数提取日期部分查询要更优于完整的时间范围查询。

如以下的索引方式

MySQL索引：
名字：idx_alarm
字段：time, name, extend
索引类型：NORMAL
索引方法：BTREE

案例这个idx_alarm是一个普通的多列索引,包含time, name, extend三个字段,使用的是BTREE索引。

WHERE DATE(time) = '2023-11-13'
1

这个条件就会命中索引。DATE(time) 对字段做了转换，但是不会打破索引关系。WHERE条件使用了time列的计算值进行等值匹配，根据索引最左前缀原则，索引包含查询条件中的列，所以会被用到。