dongquepao8653
2014-09-02 22:40从没有主键的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!
- 点赞
- 回答
- 收藏
- 复制链接分享
1条回答
为你推荐
- 从没有主键的MySQL表中获取随机行 - 已优化
- random
- sql
- mysql
- php
- 1个回答