mysql 偏移量是什么意思,MySQL：我需要获取查询中某项的偏移量

Mysql: i need to get the offset of a item in a query.

I have a image gallery: this show 6 image per stack, so when i request image 22 it shows images from 18 to 24. It should first get the offset of the image 22, then get the images from 18 to 24.

Another example: i request the image number 62(and offset 62), it will select images with offset from 60 to 66.

Is possible with a single query?

The main important thing is to get the offset value of the item that has its id equal to a number.

Thanks ;)

EDIT:

select * from images order_by updated_at offset(here i need to get the offset of the image id in this query, and the make some calculation... this is what i need, if is possible.. :d)

EDIT2:

Now I understand that I need 2 queries:

1º: get the offset of the image within the query with my custom order

2º: get the images using the offset from the first query... this I can make it alone, the first one is the problem.. :s

解决方案

If your images have sequential IDs, you may want to do the following:

SELECT *

FROM images

WHERE id >= ((? DIV 6) * 6) AND

id < (((? DIV 6) + 1) * 6)

ORDER BY id;

Replace the ? parameter in the above query with the ID of the image requested.

UPDATE: It seems that your images are not ordered by a sequential ID, but by a timestamp. Unfortunately it looks like MySQL does not support variable expressions in the LIMIT clause (Source). One option would be to use a prepared statement:

PREPARE stmt FROM

" SELECT *

FROM images

ORDER BY updated_at

LIMIT ?, 6";

SET @lower_limit := ((22 DIV 6) * 6);

EXECUTE stmt USING @lower_limit;

Another option could be:

SET @row = 0;

SELECT *

FROM images

WHERE (@row := @row + 1) BETWEEN ((3 DIV 6) * 6) + 1 and (((3 DIV 6) + 1) * 6)

ORDER BY updated_at;