mysql怎样高效率随机获取n条数据

最新推荐文章于 2025-12-13 20:00:00 发布

转载最新推荐文章于 2025-12-13 20:00:00 发布 · 1.3k 阅读

文章标签：

#MySQL

MySQL 专栏收录该内容

20 篇文章

订阅专栏

本文介绍在MySQL中如何根据ID是否连续，使用不同SQL语句随机抽取数据库记录，包括不带where语句和带where语句的情况，适用于mm_account表。

ID连续的情况下（注意不能带where，否则结果不好）：

SELECT
	*
FROM
	`mm_account` AS t1
JOIN (
	SELECT
		ROUND(
			RAND() * (
				SELECT
					MAX(id)
				FROM
					`mm_account`
			)
		) AS id
) AS t2
WHERE
	t1.id >= t2.id
ORDER BY
	t1.id ASC
LIMIT 4;

ID不连续的情况下：

SELECT
	*
FROM
	`mm_account`  
WHERE
	id >= (
		SELECT
			floor(
				RAND() * (
					SELECT
						MAX(id)
					FROM
						`mm_account`
				)
			)
	)  and city = "city_91"
AND showSex = 1
ORDER BY
	id
LIMIT 4;

假设你的表叫做mm_account，并且有一个字段叫id。最快的方法如下（随机获取5条）：

SELECT
	*
FROM
	mm_account
WHERE
	id >= (
		(
			SELECT
				MAX(id)
			FROM
				mm_account
		) - (
			SELECT
				MIN(id)
			FROM
				mm_account
		)
	) * RAND() + (
		SELECT
			MIN(id)
		FROM
			mm_account
	)
LIMIT 5;

如果带where语句，上面就不适合了。带where语句请看下面：

SELECT
	*
FROM
	`mm_account` AS t1
JOIN (
	SELECT
		ROUND(
			RAND() * (
				(
					SELECT
						MAX(id)
					FROM
						`mm_account`
					WHERE
						id < 1000
				) - (
					SELECT
						MIN(id)
					FROM
						`mm_account`
					WHERE
						id < 1000
				)
			) + (
				SELECT
					MIN(id)
				FROM
					`mm_account`
				WHERE
					id < 1000
			)
		) AS id
) AS t2
WHERE
	t1.id >= t2.id
ORDER BY
	t1.id
LIMIT 5;

原文：https://blog.youkuaiyun.com/zwc2xm/article/details/79467829