Why is my MySQL procedure that's supposed to select a random row only selecting one of three sp-CodePudding

I have a stored MySQL procedure called quote_of_the_day that is supposed to select a different row from the quotes table each day, using the date as the seed value. When I test the code as a query, everything appears to run fine, BUT when I call the function in my php code (query is CALL quote_of_the_day()) it only ever selects one of three different quotes, despite the fact that there are 23 quotes in the table.

The create systax for quote_of_the_day is:

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 ;

Where am I going wrong?

CodePudding user response：

The comments above are teasing you with the answer.

Read the manual on MySQL's RAND() function:

...for equal argument values, RAND(N) returns the same value each time, and thus produces a repeatable sequence of column values. In the following example, the sequence of values produced by RAND(3) is the same both places it occurs.

Since CURDATE() returns a constant value every time you call it within the same day, passing it to RAND() makes RAND() return the same value every time you call it.

Demo:

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)

This will continue until my clock says it's a new day.

You don't actually need to pass a value to seed RAND(). It gets a seed when the MySQL server starts, and continues to generate random values.

Don't use the seed argument when you want a series of new random values, use the seed argument when you want a reproducible series of random values, for example if you're running automated tests.

Re your comment:

The argument to RAND() is an integer, but dates like '2021-11-07' are returned as a string. In a numeric context, the integer value of '2021-11-07' is 2021. Any non-numeric characters are stripped off before passing it as the argument to 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)

You can see that it's doing this:

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

All these dates are seeding the randomizer as if you had passed only '2021' or 2021 as the argument:

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)

CodePudding user response：

Your seed is constant to for the year of the date because when MySQL converts strings to dates it uses the first string of digits it finds in the string, which is currently 2021.

Use TO_DAYS() Seed RAND() with the number of days since year 0:

RAND(TO_DAYS(CURDATE()))

Every day will forever pass a different, but constant for the current date, value to rand()