为什么我的MySQL程序应该选择一个随机行,而只选择三个特定值中的一个?

DELIMITER ;; CREATE DEFINER=`root`@`%` PROCEDURE `quote_of_the_day`() BEGIN # save the number of rows into a variable SET @num_rows = (SELECT COUNT(*) FROM `quotes`); # calculate a random number no greater than the number of rows in the table SET @rand_num = (SELECT FLOOR(RAND(CURDATE())*(@num_rows+1))); # select random quote from the list of quotes (seed value is the current day) SELECT quote FROM quotes WHERE id = @rand_num; # increment the quoted column to keep track of which quotes are selected UPDATE quotes SET quoted = quoted + 1 WHERE id = @rand_num AND last_used <> CURDATE(); UPDATE quotes SET last_used = CURDATE() WHERE id = @rand_num; END;; DELIMITER ;

上面的评论是在用答案取笑你。

请阅读MySQL上的手册 RAND() function :

...对于相等的参数值,RAND(N)每次返回相同的值,从而生成一个可重复的列值序列。在下面的示例中,RAND(3)生成的值序列在两个位置都是相同的。

由于CURDATE()在同一天内每次调用时都返回一个常量值,因此将其传递给RAND()会使RAND()在每次调用时都返回相同的值。

演示:

mysql> select rand(curdate());
+------------------+
| rand(curdate())  |
+------------------+
| 0.49455075570806 |
+------------------+
1 row in set (0.00 sec)

mysql> select rand(curdate());
+------------------+
| rand(curdate())  |
+------------------+
| 0.49455075570806 |
+------------------+
1 row in set (0.00 sec)

mysql> select rand(curdate());
+------------------+
| rand(curdate())  |
+------------------+
| 0.49455075570806 |
+------------------+
1 row in set (0.00 sec)

mysql> select rand(curdate());
+------------------+
| rand(curdate())  |
+------------------+
| 0.49455075570806 |
+------------------+
1 row in set (0.00 sec)

这将一直持续到我的时钟显示这是新的一天。

实际上不需要将值传递给seed RAND()。当MySQL服务器启动时,它会得到一个种子,并继续生成随机值。

当需要一系列新的随机值时,不要使用seed参数,当需要 可复制的随机值序列 ,例如,如果您正在运行自动测试。

请回复您的评论:

RAND()的参数是一个整数,但像“2021-11-07”这样的日期将作为字符串返回。在数值上下文中,“2021-11-07”的整数值为2021。任何非数字字符在作为参数传递给RAND()之前都会被去除。

mysql> select rand('2021-11-04');
+--------------------+
| rand('2021-11-04') |
+--------------------+
| 0.7752841103591808 |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select rand('2021-11-05');
+--------------------+
| rand('2021-11-05') |
+--------------------+
| 0.7752841103591808 |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select rand('2021-11-06');
+--------------------+
| rand('2021-11-06') |
+--------------------+
| 0.7752841103591808 |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select rand('2021-11-07');
+--------------------+
| rand('2021-11-07') |
+--------------------+
| 0.7752841103591808 |
+--------------------+
1 row in set, 1 warning (0.00 sec)

你可以看到它是这样做的:

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '2021-11-07' |
+---------+------+-------------------------------------------------+

所有这些日期都在播种随机数,好像你只通过了“2021”或2021作为参数:

mysql> select rand('2021');
+--------------------+
| rand('2021')       |
+--------------------+
| 0.7752841103591808 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand(2021);
+--------------------+
| rand(2021)         |
+--------------------+
| 0.7752841103591808 |
+--------------------+
1 row in set (0.00 sec)