在不改变表结构的情况下，选择表中最后n行最有效的方法是什么?

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行数的最有效方法是什么?这个表包含数百万行，在任何时候我都不知道这个表有多大(它一直在增长)。该表确实有一个列，该列会自动递增，并作为每一行的唯一标识符使用。

8 个解决方案

#1

SELECT * FROM table_name ORDER BY auto_incremented_id DESC LIMIT n

#2

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行”的解决方案，但这很可能是您所需要的。

#3

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.

唯一的问题是,你可能想要选择一个不同的顺序,和这个问题使我不得不通过计算选择最后一行的行数,然后选择使用限制,但很明显,这可能不是一个好的解决方案。

#4

(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”的回答)我最喜欢的是max()- MySQL的功能，用一个简单的一行字，但是还有其他方法可以确定:

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个条目。这是一种很短的方法，可以避免错误1111(“无效使用group函数”)，而不仅仅是在有一个auto_incre- row(这里是id)的情况下。max()函数可以有多种用法。

#5

Use ORDER BY to sort by the identifier column in DESC order, and use LIMIT to specify how many results you want.

使用ORDER按DESC的顺序对标识符列进行排序，并使用LIMIT指定需要多少结果。

#6

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中调用的任何东西)，以便在需要经常执行的情况下快速进行选择。

#7

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作为唯一的行标识符。当您在一些colum(s)中有大量的数据(例如图像(blobs))时，这一点也更有用。在这种情况下，订单可能会耗费大量的时间和数据。

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".

唯一的问题是如果在需要的间隔中删除行。在这种情况下，你不会得到真正的“你想要的号码”。

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.

当您需要在多个web页面中向后显示表时，您也可以使用它来为每个页面选择n行(只需将NumberOfRowsYouWant+1)乘以页码。

#8

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;

通过emp_id desc限制n从员工订单中选择*通过emp_id asc选择订单;

#1

SELECT * FROM table_name ORDER BY auto_incremented_id DESC LIMIT n

#2