随机选择表mysql

Question

我有3个相同结构的表，分别称为低、中、高。我想从这些表中随机选择一个表，然后查询该表。

可视化示例：

SELECT * FROM RAND(low,med,high)

Answer 1

您使用的是PHP标记，因此在该语言中可以是这样的：

$tables = ['low', 'med', 'high'];
$randomTable = $tables[mt_rand(0, 2)];
$query = 'SELECT * FROM '.$randomTable;

$mysqli = new mysqli("host","my_user","my_password","my_db");
$result = $mysqli -> query( $query);

在MySQL中，除了其他解决方案之外，还可以是这样的：

随机选择表：

set @randomNumber = FLOOR(RAND()*3+1);

set @table = IF(@randomNumber = 1, 'low', IF(@randomNumber = 2, 'med', 'high'));

或

set @table = CASE
    WHEN @randomNumber = 1 THEN 'low'
    WHEN @randomNumber = 2 THEN 'med'
    ELSE 'high'
END;

构建并运行查询：

SET @s = CONCAT('select * from ', @table); 
PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

How to select from MySQL where Table name is Variable

MySQL get a random value between two values

Answer 2

您可以使用联合方法，将计算值1、2或3分配给这三个表中的每个表，然后从一个表中随机选择记录：

SELECT *
FROM
(
    SELECT *, 1 AS label FROM low UNION ALL
    SELECT *, 2 FROM med UNION ALL
    SELECT *, 3 FROM high
) t
WHERE label = FLOOR(RAND()*3+1);  -- random number between 1 and 3 inclusive

Answer 3

select * from high
union all
select * from med
union all
select * from low
order by rand()
limit 1

SQLFiddle demo