用MySQL里的Rand()生成 不连续重复 的随机数年龄以及随机姓名字符串

前言：
          RAND函数， 返回在范围0到1.0内的随机浮点值。如果一个整数参数N被指定，它被用作种子值。

一，朋友问题描述如下：
(1)，有一个用户表，有id，用户的真实名字，年龄。
通过一段存储过程来实现向表里插入100数据，其中年龄随机
姓名 
姓氏：’赵钱孙李周吴郑王’;，随机取出一个
名字：’一段文字字符串’，随机的取出一个字或者两个字
要求：随机的名字不能出现连续重复。

(2)，然后我写出的代码如下：
-- 创建用户表，在project里
create table user1 (
id int not null primary key auto_increment,
name varchar(3) not null,
age tinyint not null
) charset utf8;

-- 创建存储过程
delimiter $$
create procedure pro3(num int)
begin
declare fname char(1);
declare name1 char(1);
declare name2 char(1);
declare fullname varchar(3);
declare age int;
declare i int default 1;

while i <=num do
set fname = substring('赵钱孙李周吴郑王',floor(1+8*rand()),1);  -- 姓
set name1 = substring('一二三四五六七八九十甲乙丙丁',floor(1+14*rand()),1); -- 抽一个字
set name2 = substring('一二三四五六七八九十甲乙丙丁',floor(1+14*rand()),1); -- 抽一个字

if round(rand())=0 then  -- 创造一个局部变量fullname代表全名，随机确定是两个字还是三个字
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());  -- 年龄为20-50的随机
insert into user1 values(null,fullname,age);  -- 插入数据
set i = i + 1; 
end while;
end
$$
delimiter ;

(3)，执行结果
call pro3(100); -- 执行过程
出现的结果出乎意料，出现了很多连续一样的姓名，执行结果如下：
mysql> select * from user1;
+-----+--------+-----+
| id  | name   | age |
+-----+--------+-----+
|   1 | 赵三       |  28 |
|   2 | 孙一四      |  50 |
|   3 | 郑丁九      |  42 |
|   4 | 王八       |  47 |
|   5 | 钱六      |  48 |
|   6 | 李五       |  26 |
|   7 | 李五       |  41 |
|   8 | 郑二三      |  22 |
|   9 | 孙七六       |  24 |
|  10 | 孙三       |  21 |
|  11 | 钱甲二     |  26 |
|  12 | 孙丙       |  42 |
|  13 | 孙丙       |  37 |
|  14 | 赵六丁       |  24 |
|  15 | 赵六丁       |  40 |
|  16 | 王十七      |  28 |
|  17 | 孙九       |  22 |
|  18 | 李二       |  23 |
|  19 | 李十      |  23 |
|  20 | 赵丁甲      |  50 |
|  21 | 钱一九     |  49 |
|  22 | 钱一九     |  22 |
|  23 | 钱一九     |  30 |
|  24 | 钱一九     |  30 |
|  25 | 孙五       |  46 |
|  26 | 吴三十      |  39 |
|  27 | 赵七二      |  50 |
|  28 | 赵七二      |  29 |
|  29 | 孙甲九      |  20 |
|  30 | 郑乙十     |  35 |
|  31 | 周一      |  50 |
|  32 | 吴甲七       |  50 |
|  33 | 李丁十     |  47 |
|  34 | 李丁十     |  29 |
|  35 | 李丁十     |  40 |
|  36 | 孙五       |  47 |
|  37 | 赵九丙     |  41 |
|  38 | 郑三八      |  43 |
|  39 | 钱七      |  37 |
|  40 | 钱二一     |  24 |
|  41 | 王七五       |  45 |
|  42 | 李十四      |  24 |
|  43 | 李十四      |  28 |
|  44 | 孙甲甲      |  49 |
|  45 | 郑五      |  38 |
|  46 | 赵甲      |  27 |
|  47 | 王三       |  39 |
|  48 | 王三       |  26 |
|  49 | 王三       |  22 |
|  50 | 王三       |  30 |
|  51 | 钱一九     |  44 |
|  52 | 赵四       |  30 |
|  53 | 王十九      |  50 |
|  54 | 王十九      |  34 |
|  55 | 王十九      |  40 |
|  56 | 周丙甲      |  20 |
|  57 | 孙十      |  47 |
|  58 | 王丙       |  32 |
|  59 | 王六       |  43 |
|  60 | 吴三甲       |  29 |
|  61 | 王乙       |  26 |
|  62 | 赵甲      |  46 |
|  63 | 赵甲      |  23 |
|  64 | 王八丙      |  25 |
|  65 | 王八丙      |  30 |
|  66 | 周乙六       |  50 |
|  67 | 吴五八       |  21 |
|  68 | 孙五       |  42 |
|  69 | 赵丙六      |  48 |
|  70 | 周三       |  48 |
|  71 | 周三       |  47 |
|  72 | 李乙       |  46 |
|  73 | 李乙       |  40 |
|  74 | 吴十五      |  45 |
|  75 | 赵十五      |  36 |
|  76 | 赵十五      |  25 |
|  77 | 李十      |  35 |
|  78 | 李甲       |  38 |
|  79 | 周三一      |  33 |
|  80 | 周三一      |  48 |
|  81 | 钱六六      |  33 |
|  82 | 钱六六      |  49 |
|  83 | 孙十六      |  36 |
|  84 | 王二甲       |  48 |
|  85 | 王二甲       |  39 |
|  86 | 王乙       |  30 |
|  87 | 郑三      |  21 |
|  88 | 赵二五      |  29 |
|  89 | 周丙丙      |  43 |
|  90 | 钱七乙      |  35 |
|  91 | 钱七乙      |  47 |
|  92 | 钱七乙      |  38 |
|  93 | 孙乙       |  29 |
|  94 | 周乙       |  32 |
|  95 | 郑乙十     |  46 |
|  96 | 郑乙十     |  37 |
|  97 | 吴八       |  46 |
|  98 | 吴十八      |  42 |
|  99 | 王乙四       |  30 |
| 100 | 吴九十      |  50 |

二，分析原因
因为才14个汉字中取一个（数据源太少了），而且连续执行100次，有连续出现重复的随机数是正常的，应该是底层的rand算法没有考虑过滤到连续重复的情况，我们不好去改动底层的mysql源码，但是可以在存储过程中做一个判断，来过滤掉连续重复的情况，思路是如果重复，就重新取一个不重复的。

所以存储过程改动如下：

1. DELIMITER $$
   
2. USE `test`$$
   
3. DROP PROCEDURE IF EXISTS `pro3`$$
   
4. CREATE PROCEDURE `pro3`(num INT)
   
5. BEGIN
   
6. DECLARE fname CHAR(1);
   
7. DECLARE name1 CHAR(1);
   
8. DECLARE name2 CHAR(1);
   
9. DECLARE fullname VARCHAR(3);
   
10. DECLARE pre_fullname VARCHAR(3) DEFAULT \'\';
    
11. DECLARE age INT;
    
12. DECLARE i INT DEFAULT 1;
    
13. WHILE i <=num DO
    
14.     SET fname = SUBSTRING(\'赵钱孙李周吴郑王\',FLOOR(1+8*RAND()),1); 
    
15.     SET name1 = SUBSTRING(\'一二三四五六七八九十甲乙丙丁\',ROUND(1+14*RAND()),1); 
    
16.     SET name2 = SUBSTRING(\'一二三四五六七八九十甲乙丙丁\',ROUND(1+14*RAND()),1); 
    
17. 
    
18.     IF ROUND(RAND())=0 THEN 
    
19.     SET fullname = CONCAT(fname,name1);
    
20.     END IF;
    
21.     IF ROUND(RAND())=1 THEN
    
22.     SET fullname = CONCAT(fname,name1,name2);
    
23.     END IF;
    
24. 
    
25.     SET age = FLOOR(20+31*RAND()); 
    
26.     /* 给临时变量赋值，看最新的一条记录的name值 */
    
27.     SELECT NAME INTO pre_fullname FROM user1 ORDER BY id DESC LIMIT 1;
    
28.     /* 开始判断，如果刚赋值的name跟上一次赋值的name值一样，就跳过，i保持不变，继续取值；否则i+1，继续循环取值*/
    
29.     IF fullname=pre_fullname AND i>1 THEN
    
30.         SET i=i;
    
31.     ELSEIF fullname IS NULL THEN
    
32.         SET i=i;        
    
33.     ELSE
    
34.         INSERT INTO user1 VALUES(NULL,fullname,age); 
    
35.         SET i = i + 1; 
    
36.     END IF ;
    
37. END WHILE;
    
38. END$$
    
39. DELIMITER

三，执行改动后的存储过程，查看效果
执行结果如下：

1. mysql> DELIMITER $$
   
2. mysql> USE `test`$$
   
3. Database changed
   
4. mysql> DROP PROCEDURE IF EXISTS `pro3`$$
   
5. Query OK, 0 rows affected, 1 warning (0.00 sec)
   
6. 
   
7. mysql> CREATE PROCEDURE `pro3`(num INT)
   
8.     -> BEGIN
   
9.     -> DECLARE fname CHAR(1);
   
10.     -> DECLARE name1 CHAR(1);
    
11.     -> DECLARE name2 CHAR(1);
    
12.     -> DECLARE fullname VARCHAR(3);
    
13.     -> DECLARE pre_fullname VARCHAR(3) DEFAULT \'\';
    
14.     -> DECLARE age INT;
    
15.     -> DECLARE i INT DEFAULT 1;
    
16.     -> WHILE i <=num DO
    
17.     -> SET fname = SUBSTRING(\'赵钱孙李周吴郑王\',FLOOR(1+8*RAND()),1); 
    
18.     -> SET name1 = SUBSTRING(\'一二三四五六七八九十甲乙丙丁\',ROUND(1+14*RAND()),1); 
    
19.     -> SET name2 = SUBSTRING(\'一二三四五六七八九十甲乙丙丁\',ROUND(1+14*RAND()),1); 
    
20.     -> 
    
21.     -> IF ROUND(RAND())=0 THEN 
    
22.     -> SET fullname = CONCAT(fname,name1);
    
23.     -> END IF;
    
24.     -> IF ROUND(RAND())=1 THEN
    
25.     -> SET fullname = CONCAT(fname,name1,name2);
    
26.     -> END IF;
    
27.     -> 
    
28.     -> SET age = FLOOR(20+31*RAND()); 
    
29.     -> /* 给临时变量赋值，看最新的一条记录的name值 */
    
30.     -> SELECT NAME INTO pre_fullname FROM user1 ORDER BY id DESC LIMIT 1;
    
31.     -> /* 开始判断，如果刚赋值的name跟上一次赋值的name值一样，就跳过，i保持不变，继续取值；否则i+1，继续循环取值*/
    
32.     -> IF fullname=pre_fullname AND i>1 THEN
    
33.     -> 
    
34. Display all 799 possibilities? (y or n) 
    
35.     -> SET i=i;
    
36.     -> ELSEIF fullname IS NULL THEN
    
37.     -> 
    
38. Display all 799 possibilities? (y or n) 
    
39.     -> SET i=i;
    
40. Display all 799 possibilities? (y or n) 
    
41.     -> SET i=i;
    
42.     -> ELSE
    
43.     -> 
    
44. Display all 799 possibilities? (y or n) 
    
45.     -> INSERT INTO user1 VALUES(NULL,fullname,age); 
    
46.     -> 
    
47. Display all 799 possibilities? (y or n) 
    
48.     -> SET i = i + 1; 
    
49.     -> END IF ;
    
50.     -> END WHILE;
    
51.     -> END$$
    
52. Query OK, 0 rows affected (0.00 sec)
    
53. 
    
54. mysql> DELIMITER ;
    
55. mysql> 
    
56. mysql> truncate table user1;
    
57. Query OK, 0 rows affected (0.01 sec)
    
58. 
    
59. mysql> select * from user1;
    
60. Empty set (0.00 sec)
    
61. 
    
62. mysql> call pro3(100);
    
63. Query OK, 1 row affected (0.11 sec)
    
64. 
    
65. mysql> select * from user1;
    
66. +-----+-----------+-----+
    
67. | id | name | age |
    
68. +-----+-----------+-----+
    
69. | 1 | 吴甲九 | 32 |
    
70. | 2 | 赵丙 | 49 |
    
71. | 3 | 钱二 | 40 |
    
72. | 4 | 郑二丙 | 34 |
    
73. | 5 | 孙四三 | 39 |
    
74. | 6 | 钱五十 | 39 |
    
75. | 7 | 赵五 | 23 |
    
76. | 8 | 郑九 | 22 |
    
77. | 9 | 钱九七 | 47 |
    
78. | 10 | 郑七乙 | 39 |
    
79. | 11 | 孙丙二 | 44 |
    
80. | 12 | 吴三九 | 30 |
    
81. | 13 | 钱九四 | 28 |
    
82. | 14 | 王十九 | 49 |
    
83. | 15 | 周丙乙 | 43 |
    
84. | 16 | 王十 | 33 |
    
85. | 17 | 钱乙七 | 36 |
    
86. | 18 | 赵七四 | 32 |
    
87. | 19 | 郑八二 | 31 |
    
88. | 20 | 赵二 | 35 |
    
89. | 21 | 赵丙 | 26 |
    
90. | 22 | 郑甲丁 | 38 |
    
91. | 23 | 周丙九 | 50 |
    
92. | 24 | 李丙五 | 27 |
    
93. | 25 | 李五三 | 37 |
    
94. | 26 | 孙丙六 | 46 |
    
95. | 27 | 钱八 | 37 |
    
96. | 28 | 周七六 | 40 |
    
97. | 29 | 李一一 | 45 |
    
98. | 30 | 钱十七 | 22 |
    
99. | 31 | 吴七一 | 49 |
    
100. | 32 | 吴丙六 | 39 |
     
101. | 33 | 吴四四 | 50 |
     
102. | 34 | 郑八丙 | 28 |
     
103. | 35 | 吴五七 | 26 |
     
104. | 36 | 吴甲 | 46 |
     
105. | 37 | 钱四 | 39 |
     
106. | 38 | 王丙九 | 24 |
     
107. | 39 | 王八八 | 38 |
     
108. | 40 | 吴乙丙 | 35 |
     
109. | 41 | 吴丙七 | 39 |
     
110. | 42 | 周甲丁 | 31 |
     
111. | 43 | 钱二甲 | 25 |
     
112. | 44 | 钱五丁 | 32 |
     
113. | 45 | 孙四二 | 28 |
     
114. | 46 | 李七丙 | 37 |
     
115. | 47 | 钱七七 | 46 |
     
116. | 48 | 郑乙四 | 33 |
     
117. | 49 | 钱甲丁 | 34 |
     
118. | 50 | 王五 | 37 |
     
119. | 51 | 吴一 | 44 |
     
120. | 52 | 王二十 | 33 |
     
121. | 53 | 郑四 | 41 |
     
122. | 54 | 钱九八 | 29 |
     
123. | 55 | 李十十 | 24 |
     
124. | 56 | 钱六七 | 44 |
     
125. | 57 | 王二 | 38 |
     
126. | 58 | 周五 | 36 |
     
127. | 59 | 吴九 | 44 |
     
128. | 60 | 赵丙 | 38 |
     
129. | 61 | 李五五 | 20 |
     
130. | 62 | 王八乙 | 33 |
     
131. | 63 | 王丙八 | 49 |
     
132. | 64 | 王九六 | 38 |
     
133. | 65 | 钱七 | 45 |
     
134. | 66 | 赵二二 | 36 |
     
135. | 67 | 钱五乙 | 40 |
     
136. | 68 | 孙甲七 | 43 |
     
137. | 69 | 郑甲五 | 47 |
     
138. | 70 | 赵丙 | 20 |
     
139. | 71 | 郑四 | 43 |
     
140. | 72 | 孙丙五 | 24 |
     
141. | 73 | 孙三 | 33 |
     
142. | 74 | 赵四乙 | 31 |
     
143. | 75 | 李 | 44 |
     
144. | 76 | 吴九丁 | 43 |
     
145. | 77 | 郑甲一 | 33 |
     
146. | 78 | 李三五 | 37 |
     
147. | 79 | 王二 | 31 |
     
148. | 80 | 赵七丙 | 35 |
     
149. | 81 | 吴三十 | 22 |
     
150. | 82 | 李二 | 50 |
     
151. | 83 | 李八 | 49 |
     
152. | 84 | 王一 | 27 |
     
153. | 85 | 王三 | 22 |
     
154. | 86 | 周五 | 45 |
     
155. | 87 | 郑八 | 44 |
     
156. | 88 | 郑甲二 | 48 |
     
157. | 89 | 赵乙 | 37 |
     
158. | 90 | 周四五 | 30 |
     
159. | 91 | 周二七 | 41 |
     
160. | 92 | 孙四 | 21 |
     
161. | 93 | 周丙七 | 24 |
     
162. | 94 | 孙丁乙 | 32 |
     
163. | 95 | 周九一 | 20 |
     
164. | 96 | 周九 | 32 |
     
165. | 97 | 赵二一 | 26 |
     
166. | 98 | 周丁五 | 47 |
     
167. | 99 | 孙三 | 38 |
     
168. | 100 | 吴十四 | 29 |
     
169. +-----+-----------+-----+
     
170. 100 rows in set (0.00 sec)
     
171. 
     
172. mysql>

从中看到已经连续100个随机数中，没有出现连续重复的姓名和年龄。


四，RAND()与RAND(N)区别
每个种子产生的随机数序列是不同的，如果传入N参数执行RAND(N)，则每次查询到的随机数都是固定的，如果没有传入N参数，则每次都是随机的值。如下所示：

1. mysql> SELECT RAND(),RAND(5);
   
2. +--------------------+---------------------+
   
3. | RAND() | RAND(5) |
   
4. +--------------------+---------------------+
   
5. | 0.7946587333405938 | 0.40613597483014313 |
   
6. +--------------------+---------------------+
   
7. 1 row in set (0.00 sec)
   
8. 
   
9. 
   
10. mysql> SELECT RAND(),RAND(5);
    
11. +---------------------+---------------------+
    
12. | RAND() | RAND(5) |
    
13. +---------------------+---------------------+
    
14. | 0.12910866749389904 | 0.40613597483014313 |
    
15. +---------------------+---------------------+
    
16. 1 row in set (0.00 sec)
    
17. 
    
18. 
    
19. mysql> SELECT RAND(),RAND(5);
    
20. +-------------------+---------------------+
    
21. | RAND() | RAND(5) |
    
22. +-------------------+---------------------+
    
23. | 0.261567168181359 | 0.40613597483014313 |
    
24. +-------------------+---------------------+
    
25. 1 row in set (0.00 sec)
    
26. 
    
27. 
    
28. mysql> SELECT RAND(),RAND(5);
    
29. +--------------------+---------------------+
    
30. | RAND() | RAND(5) |
    
31. +--------------------+---------------------+
    
32. | 0.9205098691587428 | 0.40613597483014313 |
    
33. +--------------------+---------------------+
    
34. 1 row in set (0.00 sec)
    
35. 
    
36. 
    
37. mysql> SELECT RAND(),RAND(5);
    
38. +--------------------+---------------------+
    
39. | RAND() | RAND(5) |
    
40. +--------------------+---------------------+
    
41. | 0.8178478719832821 | 0.40613597483014313 |
    
42. +--------------------+---------------------+
    
43. 1 row in set (0.00 sec)
    
44. 
    
45. 
    
46. mysql>