Use Rand () in MySQL to generate random numbers with random numbers and random name strings.

Use Rand () in MySQL to generate random numbers with random numbers and random name strings.
Preface:
The RAND function returns a random floating point value ranging from 0 to 1.0. If an integer parameter N is specified, it is used as a seed value.
1. A friend's question is described as follows:
A stored procedure is used to insert 100 data records into the table, where the age is random.
(2), then the code I wrote is as follows:
-- Create a user table in the project
(3) execution result
Call pro3 (100); -- Execution Process
Ii. analyze the cause

Therefore, the stored procedure changes as follows:

DELIMITER $ USE 'test' $ drop procedure if exists 'pro3' $ create procedure 'pro3' (num INT) BEGINDECLARE fname CHAR (1 ); DECLARE name1 CHAR (1); DECLARE name2 CHAR (1); DECLARE fullname VARCHAR (3); DECLARE pre_fullname VARCHAR (3) DEFAULT \ '; DECLARE age INT; DECLARE I int default 1; WHILE I <= num DOSET fname = SUBSTRING (\ 'zhao Qian Sun Li Zhou Wu Zheng Wang \ ', FLOOR (1 + 8 * RAND (), 1 ); SET name1 = SUBSTRING (\ 'August 5, 1234, 80 or 90, A, B, C, and D \ ', ROUND (1 + 14 * RAND (), 1); SET name2 = SUBSTRING (\ 'August 5, 1234 80 or 90 A, B, C, ', ROUND (1 + 14 * RAND (), 1 ); if round (RAND () = 0 THENSET fullname = CONCAT (fname, name1); end if; if round (RAND () = 1 THENSET fullname = CONCAT (fname, name1, name2); end if; SET age = FLOOR (20 + 31 * RAND ();/* assign values to the temporary variable, view the name value of the latest record */select name into pre_fullname FROM user1 order by id desc limit 1;/* start to judge, IF the name you just assigned is the same as the name value of the previous assignment, it will be skipped. I will remain unchanged and continue with the value; otherwise, I + 1 will continue with the loop value */IF fulln Ame = pre_fullname AND I> 1 THENSET I = I; ELSEIF fullname is null thenset I = I; elseinsert into user1 VALUES (NULL, fullname, age); SET I = I + 1; end if; end while; END $ DELIMITER 3. Execute the modified stored procedure, view results mysql> DELIMITER $ mysql> USE 'test' $ Database changedmysql> drop procedure if exists 'pro3' $ Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create procedure 'pro3' (num INT)-> BEGIN-> DECLARE fname CHAR (1);-> DECLARE name1 CHAR (1);-> DECLARE name2 CHAR (1);-> DECLARE fullname VARCHAR (3);-> DECLARE pre_fullname VARCHAR (3) DEFAULT \'\'; -> DECLARE age INT;-> DECLARE I int default 1;-> WHILE I <= num DO-> SET fname = SUBSTRING (\ 'zhao Qian Sun Li Zhou Wu Zheng Wang \', FLOOR (1 + 8 * RAND (), 1);-> SET name1 = SUBSTRING (\ 'August 5, 80 or 90, A, B, and C \', ROUND (1 + 14 * RAND (), 1);-> SET name2 = SUBSTRING (\ 'August 5, 80 or 90, A, B, C, and D \', ROUND (1 + 14 * RAND (), 1);-> if round (RAND () = 0 THEN-> SET fullname = CONCAT (fname, name1);-> end if;-> if round (RAND ()) = 1 THEN-> SET fullname = CONCAT (fname, name1, name2);-> end if;-> SET age = FLOOR (20 + 31 * RAND ()); ->/* assign values to temporary variables and check the name value of the latest record */-> select name into pre_fullname FROM user1 order by id desc limit 1; ->/* start to judge. If the name just assigned is the same as the name value of the previous assignment, it will be skipped. I will remain unchanged and the value will continue; otherwise, I + 1, continue loop value */-> IF fullname = pre_fullname AND I> 1 THEN-> Display all 799 poss Ibilities? (Y or n)-> SET I = I;-> ELSEIF fullname IS NULL THEN-> Display all 799 possibilities? (Y or n)-> SET I = I; Display all 799 possibilities? (Y or n)-> SET I = I;-> ELSE-> Display all 799 possibilities? (Y or n)-> insert into user1 VALUES (NULL, fullname, age);-> Display all 799 possibilities? (Y or n)-> SET I = I + 1;-> end if;-> end while;-> END $ Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER; mysql> truncate table user1; Query OK, 0 rows affected (0.01 sec) mysql> select * from user1; Empty set (0.00 sec) mysql> call pro3 (100); Query OK, 1 row affected (0.11 sec) mysql> select * from user1; + ----- + ----------- + ----- + | id | name | age | + ----- + ----------- + ----- + | 1 | Wu Jia JIU | 32 | 2 | Zhao Bing | 49 | 3 | Qian 'er | 40 | 4 | Zheng er Bing | 34 | 5 | SUN Si San | 39 | 6 | Qian 50 | 39 | 7 | Zhao Wu | 23 | | 8 | Zheng JIU | 22 | 9 | Qian jiu qi | 47 | 10 | Zheng Qi Yi | 39 | 11 | sun Bing 2 | 44 | 12 | Wu sanjiu | 30 | 13 | Qian JIU Si | 28 | 14 | Wang 19 | 49 | 15 | Zhou Yi | 43 | 16 | Wang Shi | 33 | 17 | Qian Yi 7 | 36 | 18 | Zhao qisi | 32 | 19 | Zheng Babu | 31 | 20 | Zhao 'er | 35 | 21 | Zhao Bing | 26 | 22 | zheng jiating | 38 | 23 | Zhou bingjiu | 50 | 24 | Li bingwu | 27 | 25 | Li wusan | 37 | 26 | sun bingliu | 46 | | 27 | Qian Ba | 37 | 28 | week 7 | 40 | 29 | Li Yi | 45 | 30 | Qian 17 | 22 | 31 | Wu 7yi | 49 | | 32 | Wu Bing 6 | 39 | 33 | Wu Si 4 | 50 | 34 | Zheng Ba Bing | 28 | 35 | Wu Wuqi | 26 | 36 | Wu Jia | 46 | 37 | Qian Si | 39 | 38 | Wang bingjiu | 24 | 39 | Wang Babu | 38 | 40 | Wu EPDM | 35 | 41 | Wu bing Qi | 39 | 42 | Zhou jiating | 31 | 43 | Qian dimethyl | 25 | 44 | Qian Wuding | 32 | 45 | sun si'er | 28 | 46 | li qibing | 37 | 47 | Qian Qiqi | 46 | 48 | Zheng Yisi | 33 | 49 | Qian jiating | 34 | 50 | Wang Wu | 37 | 51 | Wu Yi | 44 | 52 | Wang 20 | 33 | 53 | Zheng Si | 41 | 54 | Qian jiuba | 29 | 55 | Li Shishi | 24 | 56 | qian Liuqi | 44 | 57 | Wang 'er | 38 | 58 | Friday | 36 | 59 | Wu JIU | 44 | 60 | Zhao Bing | 38 | 61 | Li wuwu | 20 | 62 | | 33 | 63 | | 49 | 64 | | 38 | 65 | Qian Qi | 45 | 66 | Zhao II | 36 | 67 | Qian Wuyi | 40 | 68 | sun jiaqi | 43 | 69 | Zheng JiaWu | 47 | 70 | Zhao Bing | 20 | 71 | Zheng Si | 43 | 72 | sun bingwu | 24 | 73 | sun San | 33 | 74 | Zhao Siyi | 31 | 75 | Li | 44 | 76 | Wu jiuding | 43 | 77 | Zheng jianyi | 33 | 78 | Li sanwu | 37 | 79 | Wang 'er | 31 | 80 | Zhao qibing | 35 | | 81 | Wu 30.22 | 82 | Li 'er | 50 | 83 | LI Ba | 49 | 84 | Wang Yi | 27 | 85 | Wang San | 22 | 86 | friday | 45 | 87 | Zheng Ba | 44 | 88 | Zheng Jia 2 | 48 | 89 | Zhao Yi | 37 | 90 | Thursday 5 | 30 | 91 | tuesday 7 | 41 | 92 | SUN Si | 21 | 93 | Zhou Bing 7 | 24 | 94 | sun Ding B | 32 | 95 | Zhou Jiu Yi | 20 | 96 | week 9 | 32 | 97 | Zhao eryi | 26 | 98 | Zhou Ding Wu | 47 | 99 | sun San | 38 | 100 | Wu 14th | 29 | + ----- + ----------- + ----- + 100 rows in set (0.00 sec)

Mysql> we can see that there are no consecutive duplicate names and ages among the 100 consecutive random numbers.
4. Differences between RAND () and RAND (N)
The random number sequence generated by each seed is different. If the N parameter is input and RAND (N) is executed, the random number queried each time is fixed. If N parameter is not input, then each time is a random value. As follows:

mysql> SELECTRAND(),RAND(5);+--------------------+---------------------+| RAND() | RAND(5) |+--------------------+---------------------+| 0.7946587333405938 | 0.40613597483014313 |+--------------------+---------------------+1 row in set (0.00 sec)mysql> SELECT RAND(),RAND(5);+---------------------+---------------------+| RAND() | RAND(5) |+---------------------+---------------------+| 0.12910866749389904 | 0.40613597483014313 |+---------------------+---------------------+1 row in set (0.00 sec)mysql> SELECT RAND(),RAND(5);+-------------------+---------------------+| RAND() | RAND(5) |+-------------------+---------------------+| 0.261567168181359 | 0.40613597483014313 |+-------------------+---------------------+1 row in set (0.00 sec)mysql> SELECT RAND(),RAND(5);+--------------------+---------------------+| RAND() | RAND(5) |+--------------------+---------------------+| 0.9205098691587428 | 0.40613597483014313 |+--------------------+---------------------+1 row in set (0.00 sec)mysql> SELECT RAND(),RAND(5);+--------------------+---------------------+| RAND() | RAND(5) |+--------------------+---------------------+| 0.8178478719832821 | 0.40613597483014313 |+--------------------+---------------------+1 row in set (0.00 sec)mysql>