This is supposed to be able to return a random post id. It is also said to be the fastest method using MySQL.
SELECT t.id
FROM table t
JOIN (SELECT(FLOOR(max(id) * rand())) AS maxid FROM table)
AS tt
ON t.id >= tt.maxid
L开发者_高级运维IMIT 1
Can't seem to wrap my head around this, please help.
You pick a random ID number from your table with the "joined table"
SELECT(FLOOR(max(id) * rand())) AS maxid FROM table
This will only return something you can use if the id's don't have holes in them (consequtive integers). THat's why you're joining on this
ON t.id >= tt.maxid
You'll get all the id's that are HIGHER then your random number. The limit then gets the first of those.
So an example: say you have these entries in table: 1, 2, 5, 7, 8.
The floored random number will be smaller then 8, e.g. 3. You'll join will give you 5, 7 and 8 but returns only 5 because of the limit
First, formatting helps
1. SELECT t.id
2. FROM table t
3. JOIN (SELECT(FLOOR(max(id) * rand())) AS maxid FROM table) AS tt
4. ON t.id >= tt.maxid
5. LIMIT 1
Let's look at subquery inside JOIN first:
- rand() returns random value in range [0, 1] (e.g. 0.999, 0.251, 1)
- max(id) means that your query will return maximal value of column
id
from specified table - Floor rounds input number down to the nearest integer (e.g. Floor(5.1) == 5, Floor(5.9) = 5)
hence, the subquery will return you random integer (due to FLOOR) number in the
range [0, 1]
"LIMIT 1" means that your query will return only one row from resulting set.
Hope this enough. Feel free to ask more details
精彩评论