MySQL如何实现带距离过滤、having条件的符合数据总条数统计

问题原因 #

你之前的错误写法同时包含聚合函数count(id)和非聚合字段（id、colone等），属于非法的聚合查询，同时保留了limit 0,50分页限制，所以既拿不到全量统计结果，还会返回多余的行数据。

正确SQL写法（和你原有筛选逻辑100%匹配） #

写法1（简洁版，性能无差异）

直接把距离计算放到HAVING条件做过滤，不需要返回多余字段、不需要排序和分页限制：

SELECT COUNT(id) AS total_count
FROM mytableabc 
WHERE colone = 'sampleforsomething' 
  AND MATCH(colone,coltwo) AGAINST('searchkeywordabc' IN BOOLEAN MODE)
HAVING ROUND((6371 * ACOS(COS(RADIANS('40.123456')) * COS(RADIANS(latitude)) * COS(RADIANS(longtitude) - RADIANS('29.123456')) + SIN(RADIANS(latitude)) * SIN(RADIANS('40.123456')))), 2) <= 5;

写法2（易维护版，逻辑和原查询完全对齐）

用子查询嵌套，后续修改筛选条件时直接和原查询同步内层逻辑即可，不容易出错：

SELECT COUNT(id) AS total_count
FROM (
    SELECT id
    FROM mytableabc 
    WHERE colone = 'sampleforsomething' 
      AND MATCH(colone,coltwo) AGAINST('searchkeywordabc' IN BOOLEAN MODE)
    HAVING ROUND((6371 * ACOS(COS(RADIANS('40.123456')) * COS(RADIANS(latitude)) * COS(RADIANS(longtitude) - RADIANS('29.123456')) + SIN(RADIANS(latitude)) * SIN(RADIANS('40.123456')))), 2) <= 5
) AS temp;

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