如何获取 MySQL 中特定行的偏移量? [英] How can I get the offset of a particular row in MySQL?

问题描述

我正在尝试制作一个图像数据库，它不会保持一致的 ID 记录.例如，它可能是 1、2、6、7、12，但正如您所见，它只有 5 行.在表中，我有文件 ID 和文件名.我创建了一个 PHP 脚本来在我提供文件 ID 时向我显示图像.但是，如果我给它不存在的 ID 5，我会收到错误消息.这很好，因为我想要一个错误，但不适用于使用前进和后退按钮浏览这些图像的用户.前进和后退按钮需要检索给定 ID 之后的真实文件 ID.希望这是有道理的.

I'm trying to make an image database which does not keep a consistent record of ID's. For example it might go 1,2,6,7,12, but as you can see that is only 5 rows. Inside the table I have fileid and filename. I created a PHP script to show me the image when I give the fileid. But if I give it the ID 5 which does not exist I get an error. That's fine as I want an error for that, but not for users who will browse through these images using forward and back buttons. The forward and back buttons would need to retrieve the true fileid which comes after the given ID. Hopefully that makes sense.

这是我想象的代码的样子:

This is how I imagine the code to look like:

SELECT offset( WHERE fileid=4 )

这会给我 fileid 等于 4 的行的偏移​​量.我认为这很容易理解.我需要这个的原因是为了创建前进和后退按钮.所以我计划从偏移量中加 1 或取 1，这会给我新的 ID 和新的文件名.这样用户浏览的时候会自动跳过死的ID值，但是给假ID会报错.

That would give me the offset of the row where fileid is equal to 4. I think this is easy enough to understand. The reasons I need this are for creating the forward and back button. So I planned to add 1 or take 1 from the offset which gives me the new ID, and the new filename. That way when users browse it will skip the dead ID values automatically, but it will give an error when giving a false ID.

推荐答案

往上走:

SELECT * FROM table WHERE id > 'your_current_id' ORDER BY id LIMIT 1;

下去:

SELECT * FROM table WHERE id < 'your_current_id' ORDER BY id DESC LIMIT 1;

ps: LIMIT 2 比较好，这样可以在只返回一条记录的情况下看到自己是在数据库中的第一条记录还是最后一条记录.

ps: it is better to make LIMIT 2, so that you can see that you are at the first or at the last records in the database when only one record is returned.

这篇关于如何获取 MySQL 中特定行的偏移量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！