使用 MySQL 选择随机行

2023-05-01数据库问题

本文介绍了使用 MySQL 选择随机行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！

问题描述

我看到很多关于这个主题的话题，但我一直没有成功理解如何去做.

例如，如果我有这张表:

+------+-------+-------+|身份证 |姓名 |类 |+------+-------+-------+|5 |测试 |一 ||10 |测试2 |一 ||12 |测试5 |一 ||7 |测试6 |二 |+------+-------+-------+

我只想显示一"类中的 X 个随机行，我该怎么做?

注意:这是一张大桌子，所以我不想使用ORDER BY RAND.

解决方案

正如您所知，大多数人推荐的 ORDER BY RAND() 解决方案不能扩展到大型表.

SET @r := (SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM mytable)));SET @sql := CONCAT('SELECT * FROM mytable LIMIT 1 OFFSET', @r);从@sql 准备 stmt1;执行 stmt1;

我在我的书中介绍了这个和其他解决方案，SQL 反模式:避免陷阱数据库编程.

<小时>

如果你想用 PHP 做到这一点，你可以这样做(未测试):

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();

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;

I cover this and other solutions in my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

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

<?php
$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();

这篇关于使用 MySQL 选择随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

The End

Mysql目录里的ibtmp1文件过大造成磁盘占满的解决办法

ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在文件系统磁盘足够的情况下，这个文件大小是可以无限增长的。为了避免ibtmp1文件无止境的暴涨导致...

2025-01-02 数据库问题

151

相关推荐

Mysql目录里的ibtmp1文件过大造成磁盘占满的解决办法

SQL 子句“GROUP BY 1"是什么意思?意思是?

MySQL groupwise MAX() 返回意外结果

MySQL SELECT 按组最频繁

为什么 Mysql 的 Group By 和 Oracle 的 Group by 行为不同

MySQL GROUP BY DateTime +/- 3 秒

热门文章

热门精品源码

最新VIP资源