mysql - MariaDB 中的奇怪行为或 RAND : Single RAND delivers more than 1 result

Question

运行以下查询时:

SELECT productid 
FROM product 
WHERE productid=ROUND(RAND()*(SELECT MAX(productid) FROM product));

结果应该是 0 或 1 个结果(0 由于数据间隙，1 如果找到记录)，但是它会多次产生多个结果(非常容易重现，90% 的查询有超过1 个结果)。

示例输出:

+-----------+
| productid |
+-----------+
|     11701 |
|     20602 |
|     22029 |
|     24994 |
+-----------+

(DB中的记录数约为30k)。

运行单个 SELECT RAND() 始终会产生单个结果。

解释:

explain SELECT productid  FROM product  WHERE productid=ROUND(RAND()*(SELECT MAX(productid) FROM product));
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+-------+----------+------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                        |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+-------+----------+------------------------------+
|  1 | PRIMARY     | product | NULL       | index | NULL          | idx_prod_url | 2003    | NULL | 31197 |    10.00 | Using where; Using index     |
|  2 | SUBQUERY    | NULL    | NULL       | NULL  | NULL          | NULL         | NULL    | NULL |  NULL |     NULL | Select tables optimized away |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+-------+----------+------------------------------+

谁能解释一下这种行为？

跟进:根据 Martin 的评论，重写了查询:

SELECT productid FROM product 
WHERE productid=(SELECT ROUND(RAND()*(SELECT MAX(productid) FROM product)));

解释:

explain SELECT productid FROM product WHERE productid=(SELECT ROUND(RAND()*(SELECT MAX(productid) FROM product)));
+----+----------------------+---------+------------+-------+---------------+--------------+---------+------+-------+----------+------------------------------+
| id | select_type          | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                        |
+----+----------------------+---------+------------+-------+---------------+--------------+---------+------+-------+----------+------------------------------+
|  1 | PRIMARY              | product | NULL       | index | NULL          | idx_prod_url | 2003    | NULL | 31197 |   100.00 | Using where; Using index     |
|  2 | UNCACHEABLE SUBQUERY | NULL    | NULL       | NULL  | NULL          | NULL         | NULL    | NULL |  NULL |     NULL | No tables used               |
|  3 | SUBQUERY             | NULL    | NULL       | NULL  | NULL          | NULL         | NULL    | NULL |  NULL |     NULL | Select tables optimized away |
+----+----------------------+---------+------------+-------+---------------+--------------+---------+------+-------+----------+------------------------------+

但是，尽管计划发生了变化，行为仍然保持不变。

跟进2:

使用INNER JOIN，该行为消失:

SELECT a.productid  FROM product a 
INNER JOIN (SELECT ROUND(RAND()*(SELECT MAX(productid))) as productid 
   FROM product) b ON a.productid=b.productid;

解释:

explain SELECT a.productid  FROM product a INNER JOIN (SELECT ROUND(RAND()*(SELECT MAX(productid))) as productid FROM product) b ON a.productid=b.productid;
+----+--------------------+------------+------------+--------+---------------+--------------+---------+-------+-------+----------+----------------+
| id | select_type        | table      | partitions | type   | possible_keys | key          | key_len | ref   | rows  | filtered | Extra          |
+----+--------------------+------------+------------+--------+---------------+--------------+---------+-------+-------+----------+----------------+
|  1 | PRIMARY            | <derived2> | NULL       | system | NULL          | NULL         | NULL    | NULL  |     1 |   100.00 | NULL           |
|  1 | PRIMARY            | a          | NULL       | const  | PRIMARY       | PRIMARY      | 4       | const |     1 |   100.00 | Using index    |
|  2 | DERIVED            | product    | NULL       | index  | NULL          | idx_prod_url | 2003    | NULL  | 31197 |   100.00 | Using index    |
|  3 | DEPENDENT SUBQUERY | NULL       | NULL       | NULL   | NULL          | NULL         | NULL    | NULL  |  NULL |     NULL | No tables used |
+----+--------------------+------------+------------+--------+---------------+--------------+---------+-------+-------+----------+----------------+

Answer 1

试试这个:

SELECT productid 
FROM product 
ORDER BY rand() LIMIT 1;

参见MySQL Select Random Records对于其他随机选择选项。