MySQL 如何选择没有 ID 字段的 SQL 表的最后 10 行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4714975/
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
How to SELECT the last 10 rows of an SQL table which has no ID field?
提问by Edward Tanguay
I have an MySQL table with 25000 rows.
我有一个包含 25000 行的 MySQL 表。
This is an imported CSV file so I want to look at the last ten rows to make sure it imported everything.
这是一个导入的 CSV 文件，所以我想查看最后十行以确保它导入了所有内容。
However, since there is no ID column, I can't say:
但是，由于没有 ID 列，我不能说：
SELECT * FROM big_table ORDER BY id DESC
What SQL statement would show me the last 10 rows of this table?
什么 SQL 语句会显示这个表的最后 10 行？
The structure of the table is simply this:
表的结构很简单：
columns are: A, B, C, D, ..., AA, AB, AC, ... (like Excel)
all fields are of type TEXT
采纳答案by Tony
SQL tables have no implicit ordering, the order has to come from the data. Perhaps you should add a field to your table (e.g. an int counter) and re-import the data.
SQL 表没有隐式排序，顺序必须来自数据。也许您应该向表中添加一个字段（例如一个 int 计数器）并重新导入数据。
However that will only give the order of the import and not the data. If your data has no ordering you have to find out how to add it.
但是，这只会给出导入的顺序，而不是数据。如果您的数据没有排序，您必须找出如何添加它。
EDIT:you say
编辑：你说
...to make sure it imported everything.
...以确保它导入了所有内容。
What's wrong with using row count?
使用行数有什么问题？
回答by Ivan
All the answers here are better, but just in case... There is a way of getting 10 last added records. (thou this is quite unreliable :) ) still you can do something like
这里的所有答案都更好，但以防万一......有一种方法可以获取 10 条最后添加的记录。（你这很不可靠:)）你仍然可以做类似的事情
SELECT * FROM table LIMIT 10 OFFSET N-10
N - should be the total amount of rows in the table (SELECT count(*) FROM table). You can put it in a single query using prepared queries but I'll not get into that.
N - 应该是表中的总行数（SELECT count(*) FROM table）。您可以使用准备好的查询将它放在单个查询中，但我不会深入研究。
回答by Steven Scott
Select from the table, use the ORDER BY __DESC to sort in reverse order, then limit your results to 10.
从表中选择，使用 ORDER BY __DESC 逆序排序，然后将结果限制为 10。
SELECT * FROM big_table ORDER BY A DESC LIMIT 10
回答by Dustin Soodak
You can use the "ORDER BY DESC" option, then put it back in the original order:
您可以使用“ORDER BY DESC”选项，然后将其放回原始顺序：
(SELECT * FROM tablename ORDER BY id DESC LIMIT 10) ORDER BY id;
(SELECT * FROM tablename ORDER BY id DESC LIMIT 10) ORDER BY id;
回答by user3305213
SELECT * FROM big_table ORDER BY A DESC LIMIT 10
回答by dnagirl
If you're doing a LOAD DATA INFILE 'myfile.csv'operation, the easiest way to see if all the lines went in is to check show warnings();If you load the data into an empty or temporary table, you can also check the number of rows that it has after the insert.
如果你正在做一个LOAD DATA INFILE 'myfile.csv'操作，查看是否所有行都进入的最简单方法是检查show warnings();如果你将数据加载到一个空表或临时表中，你还可以检查它在插入后的行数。
回答by Sameeh Harfoush
executing a count(*) query on big data is expensive. i think using "SELECT * FROM table ORDER BY id DESC LIMIT n" where n is your number of rows per page is better and lighter
对大数据执行 count(*) 查询的成本很高。我认为使用“SELECT * FROM table ORDER BY id DESC LIMIT n”，其中 n 是您每页的行数更好更轻
回答by Dr.Geek Nerd
That can be done using the limit function, this might not seem new but i have added something.The code should go:
这可以使用 limit 函数来完成，这可能看起来并不新鲜，但我添加了一些东西。代码应该是：
SELECT * FROM table_name LIMIT 100,10;
for the above case assume that you have 110 rows from the table and you want to select the last ten, 100 is the row you want to start to print(if you are to print), and ten shows how many rows you want to pick from the table. For a more precised way you can start by selecting all the rows you want to print out and then you grab the last row id if you have an id column(i recommend you put one) then subtract ten from the last id number and that will be where you want to start, this will make your program to function autonomously and for any number of rows, but if you write the value directly i think you will have to change the code every time data is inserted into your table.I think this helps.Pax et Bonum.
对于上述情况，假设您有表中的 110 行，并且您想选择最后十行，100 是您要开始打印的行（如果要打印），十行显示您要选择的行数从桌子上。对于更精确的方法，您可以先选择要打印的所有行，然后如果您有 id 列（我建议您放一个），则获取最后一行 id，然后从最后一个 id 编号中减去 10，这样就可以了成为您想要开始的地方，这将使您的程序能够自主运行并针对任意数量的行运行，但是如果您直接编写该值，我认为每次将数据插入表中时您都必须更改代码。我认为这help.Pax et Bonum。
回答by mahdiaction
you can with code select 10 row from end of table. select * from (SELECT * FROM table1 order by id desc LIMIT 10) as table2 order by id"
您可以使用代码从表末尾选择 10 行。select * from (SELECT * FROM table1 order by id desc LIMIT 10) as table2 order by id"
回答by Md. Intishar Rahman
If you have not tried the following command
如果您还没有尝试过以下命令
SELECT TOP 10 * FROM big_table ORDER BY id DESC;
I see it's working when I execute the command
当我执行命令时，我看到它正在工作
SELECT TOP 10 * FROM Customers ORDER BY CustomerId DESC;
in the Try it yourselfcommand window of https://www.w3schools.com/sql/sql_func_last.asp
在https://www.w3schools.com/sql/sql_func_last.asp的Try it yourself命令窗口中
