从没有主键的MySQL表中获取随机行 - 已优化

So here's my mission: return a random row from a MySQL table with no primary key. A quick search returned this page and this solution:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1;

Unfortunately, the solution is not optimized, as this site makes clear. They propose the following fix, which is in PHP:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );

Good enough as I'll be publishing this on a page, but in the meantime I need to try it in raw MySQL. So I punched in:

SELECT * 
FROM `table` 
LIMIT (SELECT FLOOR(RAND() * COUNT(*)) FROM `table`), 1;

... and got the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT FLOOR(RAND() * COUNT(*)) FROM `table`), 1' at line 3

But I don't understand, because the individual components work perfectly. If I enter:

SELECT FLOOR(RAND() * COUNT(*)) 
FROM `table`

... I get a random number from 0 up to the number of rows minus 1. Likewise, if I enter:

SELECT * 
FROM `table` 
LIMIT 2, 1

... I get the third row in the table (or the fourth row if I substitute 3 for 2, etc.)

What am I doing wrong?? Thanks in advance!

查看全部
dongquepao8653
dongquepao8653
2014/09/02 22:40
  • random
  • php
  • mysql
  • sql
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复