mysql 从最后 n 行中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/573646/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql select from n last rows
提问by Nir
I have a table with index (autoincrement) and integer value. The table is millions of rows long.
我有一个带有索引（自动增量）和整数值的表。该表有数百万行长。
How can I search if a certain number appear in the last n rows of the table most efficiently?
如何最有效地搜索某个数字是否出现在表的最后 n 行中？
回答by Bill Karwin
Starting from the answergiven by @chaos, but with a few modifications:
从@chaos 给出的答案开始，但有一些修改：
You should always use
ORDER BYif you useLIMIT. There is no implicit order guaranteed for an RDBMS table. You may usuallyget 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.
你应该总是使用
ORDER BY，如果你使用LIMIT。RDBMS 表没有保证隐式顺序。您通常可以按主键的顺序获取行，但您不能依赖于此，也不能移植。如果按降序排序，则无需事先知道表中的行数。
您必须为派生表提供相关名称（又名表别名）。
Here's my version of the query:
这是我的查询版本：
SELECT `id`
FROM (
SELECT `id`, `val`
FROM `big_table`
ORDER BY `id` DESC
LIMIT $n
) AS t
WHERE t.`val` = $certain_number;
回答by Suganthan Madhavan Pillai
Might be a very late answer, but this is good and simple.
可能是一个很晚的答案，但这很好而且很简单。
select * from table_name order by id desc limit 5
This query will return a set of last 5 values(last 5 rows) you 've inserted in your table
此查询将返回一组您插入到表中的最后 5 个值（最后 5 行）
回答by M Palani Mca
Last 5 rows retrieve in mysql
在 mysql 中检索最后 5 行
This query working perfectly
此查询完美运行
SELECT * FROM (SELECT * FROM recharge ORDER BY sno DESC LIMIT 5)sub ORDER BY sno ASC
or
或者
select sno from(select sno from recharge order by sno desc limit 5) as t where t.sno order by t.sno asc
回答by Dana the Sane
Take advantage of SORT and LIMIT as you would with pagination. If you want the ith block of rows, use OFFSET.
像使用分页一样利用 SORT 和 LIMIT。如果您想要第 i 个行块，请使用 OFFSET。
SELECT val FROM big_table
where val = someval
ORDER BY id DESC
LIMIT n;
In response to Nir: The sort operation is not necessarily penalized, this depends on what the query planner does. Since this use case is crucial for pagination performance, there are some optimizations (see link above). This is true in postgres as well "ORDER BY ... LIMIT can be done without sorting " E.7.1. Last bullet
回应 Nir：排序操作不一定会受到惩罚，这取决于查询计划程序做什么。由于此用例对分页性能至关重要，因此有一些优化（请参阅上面的链接）。在 postgres 中也是如此，“ORDER BY ... LIMIT 可以在不排序的情况下完成” E.7.1。最后一颗子弹
explain extended select id from items where val = 48 order by id desc limit 10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | items | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
回答by Michael Buen
because it is autoincrement, here's my take:
因为它是自动增量，这是我的看法：
Select * from tbl
where certainconditionshere
and autoincfield >= (select max(autoincfield) from tbl) - $n
回答by Luke Madhanga
I know this may be a bit old, but try using PDO::lastInsertId. I think it does what you want it to, but you would have to rewrite your application to use PDO (Which is a lot safer against attacks)
我知道这可能有点旧，但请尝试使用PDO::lastInsertId. 我认为它可以做你想做的事，但你必须重写你的应用程序才能使用 PDO（这对攻击更安全）
