dongquepao8653 2014-09-02 22:40
浏览 221
已采纳

从没有主键的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条回答 默认 最新

  • duanlu9557 2014-09-02 23:10
    关注

    If you want a random sample, the following may be fast enough:

    SELECT *
    FROM `table` t cross join
         (select count(*) as cnt from table t) const
    WHERE rand() <= 100 / cnt
    ORDER BY rand()
    LIMIT 1 ;
    

    This does have to scan the table, but the sort is on roughly 100 rows, which should be fast enough.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿