mysql选取随机行,使用MySQL选择随机行

I saw many topics about this subject and I have been unsuccessful in understanding how to do it.

For example, if I have this table:

+------+-------+-------+

| id | name | class |

+------+-------+-------+

| 5 | test | one |

| 10 | test2 | one |

| 12 | test5 | one |

| 7 | test6 | two |

+------+-------+-------+

and I want to show only X random rows from class "one", how can I do that?

NOTE: it's a big table, so I don't want to use ORDER BY RAND.

解决方案

The ORDER BY RAND() solution that most people recommend doesn't scale to large tables, as you already know.

SET @r := (SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM mytable)));

SET @sql := CONCAT('SELECT * FROM mytable LIMIT 1 OFFSET ', @r);

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

If you want to do this with PHP, you could do something like this (not tested):

$mysqli->begin_transaction();

$result = $mysqli->query("SELECT COUNT(*) FROM mytable")

$row = $result->fetch_row();

$count = $row[0];

$offset = mt_rand(0, $count);

$result = $mysqli->query("SELECT * FROM mytable LIMIT 1 OFFSET $offset");

...

$mysqli->commit();