MySQL实现随机获取几条数据的方法

1.随机读取连续多条记录。经过实践，可以随机读取多条连续的数据记录，里头取值的一般都是主键ID来进行最大值、最小值的读取：

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

2.随机多条数据。以下两种都是随机读取数据，查询数据速度、随机范围都基本相差不大

SELECT * FROM example_table WHERE id >= ((SELECT MAX(id) FROM example_table)-(SELECT MIN(id) FROM example_table)) * RAND() + (SELECT MIN(id) FROM example_table) LIMIT 5 SELECT * FROM example_table WHERE id>=(SELECT floor(RAND() * ((SELECT MAX(id) FROM example_table)-(SELECT MIN(id) FROM example_table)) + (SELECT MIN(id) FROM example_table))) ORDER BY id LIMIT 5

数据库的随机查询SQL

1. Oracle,随机查询20条

select * from

(

select * from 表名

order by dbms_random.value

)

where rownum <= 20

2.MS SQL Server，随机查询20条

select top 20 * from 表名order by newid()

3.My SQL:，随机查询20条

select * from 表名 order by rand() limit 20

CREATE TABLE test_gd2gd2(

idINT,

type INT,

str CHAR(3)

)

INSERT INTO test_gd2gd2

SELECT 1, 0, 'aaa' UNION ALL

SELECT 2, 0, 'bbb' UNION ALL

SELECT 3, 1, 'sss' UNION ALL

SELECT 4, 1, 'ddd' UNION ALL

SELECT 5, 2, 'ggg' UNION ALL

SELECT 6, 2, 'hhh'

mysql>SELECT

-> type,

-> (SELECT str FROM test_gd2gd2 sub

->WHERE type = main.type ORDER BY rand()

-> LIMIT 0,1) AS Rstr

->FROM

-> test_gd2gd2 main

->GROUP BY

-> type

+------+------+

| type | Rstr |

+------+------+

|0 | bbb |

|1 | ddd |

|2 | hhh |

+------+------+

3 rows in set (0.01 sec)

mysql>SELECT

-> type,

-> (SELECT str FROM test_gd2gd2 sub

->WHERE type = main.type ORDER BY rand()

-> LIMIT 0,1) AS Rstr

->FROM

-> test_gd2gd2 main

->GROUP BY

-> type

+------+------+

| type | Rstr |

+------+------+

|0 | aaa |

|1 | sss |

|2 | hhh |

+------+------+

3 rows in set (0.00 sec)

---