mysql 从最后 n 行中选择
我有一个带有索引(自动增量)和整数值的表。 该表有数百万行长。
如何最有效地搜索某个数字是否出现在表的最后 n 行中?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
从@chaos给出的答案开始,但有一个一些修改:
如果您使用
LIMIT
,则应始终使用ORDER BY
。 RDBMS 表不保证隐式顺序。 您通常可以按主键的顺序获取行,但您不能依赖于此,也不能移植。如果按降序排序,则无需事先知道表中的行数。
您必须为派生表提供一个关联名称(也称为表别名)。
这是我的查询版本:
Starting from the answer given by @chaos, but with a few modifications:
You should always use
ORDER BY
if you useLIMIT
. 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.
Here's my version of the query:
可能是一个很晚的答案,但这很好而且简单。
此查询将返回您在表中插入的一组最后 5 个值(最后 5 行)
Might be a very late answer, but this is good and simple.
This query will return a set of last 5 values(last 5 rows) you 've inserted in your table
最后 5 行在 mysql 中检索
此查询工作正常
或
Last 5 rows retrieve in mysql
This query working perfectly
or
像使用分页一样利用 SORT 和 LIMIT。 如果您想要第 i 个行块,请使用 OFFSET。
回复尼尔:
排序操作不一定会受到惩罚,这取决于查询规划器的操作。 由于此用例对于分页性能至关重要,因此需要进行一些优化(请参阅上面的链接)。 这在 postgres 中也是如此“ORDER BY ... LIMIT 可以在不排序的情况下完成” E.7.1。 最后一个项目符号
Take advantage of SORT and LIMIT as you would with pagination. If you want the ith block of rows, use OFFSET.
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
因为它是自动增量,所以这是我的看法:
because it is autoincrement, here's my take:
我知道这可能有点旧,但请尝试使用
PDO::lastInsertId
。 我认为它可以实现您想要的功能,但是您必须重写应用程序才能使用 PDO(这对于攻击来说更安全)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)