mysql从n个最后一行中选择 [英] mysql select from n last rows

问题描述

我有一个带有索引(自动递增)和整数值的表.该表长达数百万行.

I have a table with index (autoincrement) and integer value. The table is millions of rows long.

如何搜索某个数字是否最有效地出现在表的最后n行中?

How can I search if a certain number appear in the last n rows of the table most efficiently?

推荐答案

从

Starting from the answer given by @chaos, but with a few modifications:

• 如果使用LIMIT，则应始终使用ORDER BY.对于RDBMS表，没有保证的隐式顺序.您可能通常按主键的顺序获取行，但是您不能依靠它，也不是可移植的.

• You should always use ORDER BY if you use LIMIT. There is no implicit order guaranteed for an RDBMS table. You may usually get rows in the order of the primary key, but you can't rely on this, nor is it portable.

如果按降序排列，则无需事先知道表中的行数.

If you order by in the descending order, you don't need to know the number of rows in the table beforehand.

您必须为派生表提供一个相关名(又名表别名).

You must give a correlation name (aka table alias) to a derived table.

这是我的查询版本:

SELECT `id`
FROM (
    SELECT `id`, `val`
    FROM `big_table`
    ORDER BY `id` DESC
    LIMIT $n
) AS t
WHERE t.`val` = $certain_number;

这篇关于mysql从n个最后一行中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！