php - 统计数据库中的记录总数但只返回X条记录

Question

我们正在将我们的一个应用程序从 PostGreSQL 转移到 MySQL。我们在 PostGres 中有以下查询 -

SELECT 
      idrte
    , left_name
    , gacodemun
    , clsrte
    , speed
    , speed_override
    , ST_AsGeoJSON(geometry) AS geometry
    , count(idrte) OVER() AS total 
FROM 
    aq_routes 
WHERE 
    ST_CONTAINS(
        GeomFromText(
            'Polygon(($geom))'
        ),geometry) 
GROUP BY 
      idrte
    , left_name
    , gacodemun
    , clsrte
    , speed
    , speed_override
    , geometry 
ORDER BY left_name 
LIMIT 150;

我们正在尝试将此查询移植到 MySQL 广告已经提出了这个 -

SELECT 
     id AS id
   , left_name AS left_name
   , left_locality AS left_locality
   , class AS class
   , speed AS speed
   , speed_override AS speed_override
   , AsWKB(Geom) AS geom
   , count(id) AS total 
FROM road_segments 
WHERE 
   CONTAINS(
       GeomFromText(
           'Polygon(($geom))'
       ),geom) 
GROUP BY 
      id
    , left_name
    , class
    , speed
    , speed_override
    , geom 
ORDER BY left_name 
LIMIT 150;

有一些字段发生变化，但总体而言，预期功能是相同的。

您会注意到 PostGreSQL 中对 OVER() 的调用。我们正在查询的表有很多数据。所以对于用户来说，我们只显示 150 条记录，但告诉他所有记录的数量。对于 PostGres，这可以通过一次调用来实现，但 MySQL 中没有 OVER() 函数。

除了再次查询数据库之外，我们还有什么其他选择。对数据库的第二次查询会使响应时间增加至少 15 秒，有时甚至超时。

Answer 1

我认为您想使用FOUND_ROWS()和CALC_FOUND_ROWS()。您可以查看文档here .

基本思想是:

select CALC_FOUND_ROWS id, . . .
. . .

然后使用函数 FOUND_ROWS() 将值返回给应用程序。

不幸的是，要将此值作为列值获取，我认为您需要运行查询两次，本质上是:

select . . ., x.cnt
from . . . cross join
     (select count(*) as cnt from <your query here>) x
. . .