使用MySQL从表中获取随机行
为此，您可以使用PREPARE语句。让我们首先创建一个表-
mysql> create table DemoTable( FirstName varchar(100), CountryName varchar(100) );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('Adam','US');
mysql> insert into DemoTable values('Chris','AUS');
mysql> insert into DemoTable values('Robert','UK');使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+-----------+-------------+ | FirstName | CountryName | +-----------+-------------+ | Adam | US | | Chris | AUS | | Robert | UK | +-----------+-------------+ 3 rows in set (0.00 sec)
以下是在单个查询中获取随机行的查询-
mysql> set @value := ROUND((select count(*) from DemoTable) * rand());
mysql> set @query := CONCAT('select *from DemoTable LIMIT ', @value , ', 1');
mysql> prepare myStatement from @query ;
Statement prepared
mysql> execute myStatement;
+-----------+-------------+
| FirstName | CountryName |
+-----------+-------------+
| Chris | AUS |
+-----------+-------------+
1 row in set (0.00 sec)
mysql> deallocate prepare myStatement;