MySQL 在不更改表结构的情况下选择表中最后 n 行的最有效方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/118144/
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
What's the most efficient way to select the last n rows in a table without changing the table's structure?
提问by thegad
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
使用 mySQL 选择表中最后 n 行的最有效方法是什么？该表包含数百万行，在任何给定时间我都不知道该表有多大（它在不断增长）。该表确实有一列会自动递增并用作每一行的唯一标识符。
回答by Eran Galperin
SELECT * FROM table_name ORDER BY auto_incremented_id DESC LIMIT n
回答by Sergey Telshevsky
Actually the right way to get last n rows in order is to use a subquery:
实际上，按顺序获取最后 n 行的正确方法是使用子查询：
(SELECT id, title, description FROM my_table ORDER BY id DESC LIMIT 5)
ORDER BY tbl.id ASC
As this way is the only I know that will return them in right order. The accepted answer is actually a solution for "Select first 5 rows from a set ordered by descending ID", but that is most probably what you need.
因为这种方式是我所知道的唯一可以按正确顺序返回的方式。接受的答案实际上是“从按降序 ID 排序的集合中选择前 5 行”的解决方案，但这很可能是您所需要的。
回答by tron5
(Similar to "marco"s answer,)
my fav is the max()-function of MySQL too, in a simple one-liner, but there are other ways of sure:
（类似于“marco”的回答，）
我最喜欢的也是 MySQL 的 max() 函数，在一个简单的单行中，但还有其他方法可以肯定：
SELECT whatever FROM mytable WHERE id > (SELECT max(id)-10 FROM mytable);
... and you get "last id minus 10", normally the last 10 entries of that table.
It's a short way, to avoid the a error 1111 ("Invalid use of group function") not only if there is a auto_increment-row (here id).
The max()-function can be used many ways.
...你会得到“最后一个 id 减去 10”，通常是该表的最后 10 个条目。
这是一种简短的方法，不仅在存在 auto_increment-row（此处为 id）时，还可以避免错误 1111（“组函数的无效使用”）。
max() 函数可以以多种方式使用。
回答by Jeremy Ruten
Maybe order it by the unique id descending:
也许按唯一 id 降序排序：
SELECT * FROM table ORDER BY id DESC LIMIT n
The only problem with this is that you might want to select in a different order, and this problem has made me have to select the last rows by counting the number of rows and then selecting them using LIMIT, but obviously that's probably not a good solution in your case.
唯一的问题是您可能希望以不同的顺序进行选择，这个问题使我不得不通过计算行数然后使用 LIMIT 选择它们来选择最后一行，但显然这可能不是一个好的解决方案在你的情况下。
回答by Ray
You would probably also want to add a descending index (or whatever they're called in mysql) as well to make the select fast if it's something you're going to do often.
您可能还想添加一个降序索引（或它们在 mysql 中的任何名称）以及如果您经常做的事情，也可以使选择快速。
回答by Avdi
Use ORDER BY to sort by the identifier column in DESC order, and use LIMIT to specify how many results you want.
使用 ORDER BY 按 DESC 顺序的标识符列排序，并使用 LIMIT 指定您想要的结果数量。
回答by Marco
This is a lot faster when you have big tables because you don't have to order an entire table. You just use id as a unique row identifier. This is also more eficient when you have big amounts of data in some colum(s) as images for example (blobs). The order by in this case can be very time and data consuming.
当您有大桌子时，这会快很多，因为您不必订购整个桌子。您只需使用 id 作为唯一的行标识符。当某些列中有大量数据作为图像（例如（blob））时，这也更有效。在这种情况下，order by 可能非常耗时且消耗数据。
select *
from TableName
where id > ((select max(id) from TableName)-(NumberOfRowsYouWant+1))
order by id desc|asc
The only problem is if you delete rows in the interval you want. In this case you would't get the real "NumberOfRowsYouWant".
唯一的问题是如果您在所需的时间间隔内删除行。在这种情况下，您不会得到真正的“NumberOfRowsYouWant”。
You can also easily use this to select n rows for each page just by multiplying (NumberOfRowsYouWant+1) by page number when you need to show the table backwards in multiple web pages.
当您需要在多个网页中向后显示表格时，您还可以轻松地使用它来为每个页面选择 n 行，只需将 (NumberOfRowsYouWant+1) 乘以页码即可。
回答by Vipin Pandey
Here you can change table name and column name according your requirement . if you want to show last 10 row then put n=10,or n=20 ,or n=30 ...etc according your requirement.
在这里您可以根据需要更改表名和列名。如果您想显示最后 10 行，则根据您的要求输入 n=10、或 n=20、或 n=30 ...等。
select * from (select * from employee Order by emp_id desc limit n) a Order by emp_id asc;
select * from (select * from employee Order by emp_id desc limit n) a Order by emp_id asc;
