修复PostgreSQL模糊查询问题，实现DRF实验室邮箱搜索功能

我在DRF（Django Rest Framework）项目中实现搜索功能，需要编写PostgreSQL查询，返回所有在30公里范围内且邮箱（存储在user_id字段）包含用户输入关键词的实验室。例如用户输入“S”时，所有邮箱含“S”的实验室都应被返回。

我编写的查询语句如下：

SELECT *
,  ( 3596 * acos( cos( radians(33.6379647) ) 
* cos( radians( latitude ) ) * cos( radians( longitude ) - radians(73.1467503) ) +  sin( radians(33.6379647)
) * sin( radians( latitude ) ) ) ) AS distance 
FROM "Profiling_lab" 
WHERE (6371 * acos( cos( radians(33.6379647) ) * cos( radians(latitude) ) * cos( radians(longitude) - radians(73.1467503) ) + sin( radians(33.6379647) ) * sin( radians(latitude) ) ) )  <= 30 AND user_id LIKE 'Nis'  
ORDER BY distance 

当前问题：该查询仅返回user_id与用户输入关键词（如“Nis”）完全匹配的实验室，像nishat@email.com这类包含关键词的邮箱无法被匹配到。

我的DRF后端代码如下：

@api_view(['POST'])
def NEARBYLABSAFTERQUERY(request):
    global my_long, my_lat
    my_long = str(request.data['longitude'])
    my_lat = str(request.data['latitude'])
    query = request.query_params.get('query')

    print(query)
    if request.data['Patient_id'] != "" and query != "":
        print("creating search history")
        Search = SearchHistory.objects.create(
            Search=query, Patient_id_id=request.data['Patient_id'], type="Lab")
        Search.save()

    if query == None:
        print("query is none")
        query = ""


    #Postgres query to select all labs within 30km radius and having user query in their name 
    labs = Lab.objects.raw('SELECT *,  ( 3596 * acos( cos( radians('+my_lat+') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('+my_long+') ) +  sin( radians('+my_lat+')) * sin( radians( latitude ) ) ) ) AS distance FROM "Profiling_lab" WHERE (6371 * acos( cos( radians('+my_lat+') ) * cos( radians(latitude) ) * cos( radians(longitude) - radians('+my_long+') ) + sin( radians('+my_lat+') ) * sin( radians(latitude) ) ) )  <= 30 AND user_id LIKE \''+query+'\'  ORDER BY distance')
    labSerializer = LabSerializer(labs, many=True)
    return Response(labSerializer.data)

问题核心是LIKE子句的使用错误：PostgreSQL中LIKE 'Nis'仅匹配完全等于Nis的字符串，要实现包含匹配，需添加通配符并启用大小写不敏感匹配。

1. 修正SQL匹配逻辑 #

将user_id LIKE 'Nis'改为user_id ILIKE '%Nis%'：

• %是通配符，代表任意长度的任意字符（包括空字符）
• ILIKE是PostgreSQL专属的大小写不敏感匹配，避免因大小写差异漏匹配邮箱

修正后的SQL示例：

SELECT *
,  ( 3596 * acos( cos( radians(33.6379647) ) 
* cos( radians( latitude ) ) * cos( radians( longitude ) - radians(73.1467503) ) +  sin( radians(33.6379647)
) * sin( radians( latitude ) ) ) ) AS distance 
FROM "Profiling_lab" 
WHERE (6371 * acos( cos( radians(33.6379647) ) * cos( radians(latitude) ) * cos( radians(longitude) - radians(73.1467503) ) + sin( radians(33.6379647) ) * sin( radians(latitude) ) ) )  <= 30 AND user_id ILIKE '%Nis%'  
ORDER BY distance 

2. 修复DRF代码的SQL拼接问题 #

直接拼接字符串存在SQL注入风险，改用参数化查询传递变量，同时给关键词添加通配符：

@api_view(['POST'])
def NEARBYLABSAFTERQUERY(request):
    global my_long, my_lat
    my_long = str(request.data['longitude'])
    my_lat = str(request.data['latitude'])
    # 直接设置默认空字符串，简化逻辑
    query = request.query_params.get('query', "")

    print(query)
    if request.data.get('Patient_id') != "" and query != "":
        print("creating search history")
        # 无需手动调用save()，create()会自动保存
        SearchHistory.objects.create(
            Search=query, Patient_id_id=request.data['Patient_id'], type="Lab")

    # 构造带通配符的搜索模式
    search_pattern = f'%{query}%'
    # 使用参数化查询，避免SQL注入
    labs = Lab.objects.raw('''
        SELECT *, 
               (3596 * acos( cos( radians(%s) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(%s) ) + sin( radians(%s) ) * sin( radians( latitude ) ) ) ) AS distance 
        FROM "Profiling_lab" 
        WHERE (6371 * acos( cos( radians(%s) ) * cos( radians(latitude) ) * cos( radians(longitude) - radians(%s) ) + sin( radians(%s) ) * sin( radians(latitude) ) ) ) <= 30 
          AND user_id ILIKE %s  
        ORDER BY distance
    ''', [my_lat, my_long, my_lat, my_lat, my_long, my_lat, search_pattern])
    
    labSerializer = LabSerializer(labs, many=True)
    return Response(labSerializer.data)

关键修改点： #

• 用search_pattern = f'%{query}%'给关键词前后添加通配符
• 替换LIKE为ILIKE实现大小写不敏感匹配
• 使用参数化查询（将变量放入列表传递给raw()），彻底避免SQL注入
• 简化query默认值处理，删除冗余的save()调用

3. 性能优化建议 #

如果你的PostgreSQL安装了PostGIS扩展，建议改用官方地理空间函数计算距离，性能更优且代码更简洁：

SELECT *, 
       ST_Distance(ST_MakePoint(longitude, latitude)::geography, ST_MakePoint(73.1467503, 33.6379647)::geography) AS distance
FROM "Profiling_lab"
WHERE ST_DWithin(ST_MakePoint(longitude, latitude)::geography, ST_MakePoint(73.1467503, 33.6379647)::geography, 30000)
  AND user_id ILIKE '%Nis%'
ORDER BY distance

该方式支持地理空间索引，能大幅提升大数据集下的查询速度。

内容的提问来源于stack exchange，提问作者Alishbah riaz