MariaDB LIMIT语句带来的不仅仅是限制 [英] MariaDB LIMIT statement brings more than limit

问题描述

我在 registrations 表上有10,000个用户，并且希望从我的应用程序中将此查询限制为3500个用户.但是当我调查日志时，有时它的数量超过3500.我不明白为什么该查询返回的值超出限制:

I have 10,000 users on registrations table and want to limit this query to 3500 users from my application. But when I investigate the logs , sometimes it counts more than 3500. I can not understand why that query returns more than limit:

select  count(*)
    from  registrations
    where  (selectedtime IS NULL
       AND  expirationtime < NOW()
           )
    LIMIT  3500;

我在数据库上手动尝试过，发现有时超过3500

I tried manually on DB and saw sometimes more than 3500

推荐答案

您的查询仅返回1行，该行少于3500.

Your query only returns 1 row, which is less than 3500.

如果要限制要计数的行数，则需要将其放入子查询中.

If you want to limit the number of rows that are being counted, you need to put that in a subquery.

SELECT COUNT(*)
FROM (
    SELECT 1
    FROM registrations
    WHERE selectedtime IS NULL AND expirationtime < NOW()
    LIMIT 3500) AS x

这篇关于MariaDB LIMIT语句带来的不仅仅是限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！