MySQL
RAND()函数调用可以在0和1之间产生一个随机数：
?
1
2
3
4
5
6
7
mysql
SELECT
RAND(
),
RAND(
),
RAND(
)
+------------------+-----------------+------------------+
|
RAND(
)
|
RAND(
)
|
RAND(
)
|
+------------------+-----------------+------------------+
|
0.45464584925645
|
0.1824410643265
|
0.54826780459682
|
+------------------+-----------------+------------------+
1
row
in
set
(0.00
sec)
当调用一个整数参数时，RAND()使用该随机数发生器种子值。每次在给定值种子生成，RAND()会产生一个可重复的一系列数字：
?
1
2
3
4
5
6
7
mysql
SELECT
RAND(1),
RAND(
),
RAND(
)
+------------------+------------------+------------------+
|
RAND(1
)
|
RAND(
)
|
RAND(
)
|
+------------------+------------------+------------------+
|
0.18109050223705
|
0.75023211143001
|
0.20788908117254
|
+------------------+------------------+------------------+
1
row
in
set
(0.00
sec)
可以使用ORDER
BY
RAND()，随机组行或值如下：
要了解ORDER
BY
RAND()函数,
假设EMPLOYEE_TBL的表有以下记录：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql
SELECT
*
FROM
employee_tbl
+------+------+------------+--------------------+
|
id
|
name
|
work_date
|
daily_typing_pages
|
+------+------+------------+--------------------+
|
1
|
John
|
2007-01-24
|
250
|
|
2
|
Ram
|
2007-05-27
|
220
|
|
3
|
Jack
|
2007-05-06
|
170
|
|
3
|
Jack
|
2007-04-06
|
100
|
|
4
|
Jill
|
2007-04-06
|
220
|
|
5
|
Zara
|
2007-06-06
|
300
|
|
5
|
Zara
|
2007-02-06
|
350
|
+------+------+------------+--------------------+
7
rows
in
set
(0.00
sec)
现在使用以下目录：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql
SELECT
*
FROM
employee_tbl
ORDER
BY
RAND()
+------+------+------------+--------------------+
|
id
|
name
|
work_date
|
daily_typing_pages
|
+------+------+------------+--------------------+
|
5
|
Zara
|
2007-06-06
|
300
|
|
3
|
Jack
|
2007-04-06
|
100
|
|
3
|
Jack
|
2007-05-06
|
170
|
|
2
|
Ram
|
2007-05-27
|
220
|
|
4
|
Jill
|
2007-04-06
|
220
|
|
5
|
Zara
|
2007-02-06
|
350
|
|
1
|
John
|
2007-01-24
|
250
|
+------+------+------------+--------------------+
7
rows
in
set
(0.01
sec)
mysql
SELECT
*
FROM
employee_tbl
ORDER
BY
RAND()
+------+------+------------+--------------------+
|
id
|
name
|
work_date
|
daily_typing_pages
|
+------+------+------------+--------------------+
|
5
|
Zara
|
2007-02-06
|
350
|
|
2
|
Ram
|
2007-05-27
|
220
|
|
3
|
Jack
|
2007-04-06
|
100
|
|
1
|
John
|
2007-01-24
|
250
|
|
4
|
Jill
|
2007-04-06
|
220
|
|
3
|
Jack
|
2007-05-06
|
170
|
|
5
|
Zara
|
2007-06-06
|
300
|
+------+------+------------+--------------------+
7
rows
in
set
(0.00
sec)
MySql数据库随机函授Rand()可以产生0~1之间带小数的随机数，只要将括号中的参数留空，所产生的数是完全随机的，出现重复的几率不大，但是不可避免会有重复的可能，如果需要完全避免重复，可以先用随机函数产生一堆数字，然后用剔除其中的重复数这个办法来实现。例如先用循环产生一批随机数并存入数据表中，再用
select distinct colName from tableName
选出无重复的随机数予以应用
mysql的rand函数可以生成一个0到1之间的随机数，进行一定的放大即可得到一个随机数。再通过条件查询来限制新随机数没有在表中出现过。如下所示：SELECT FLOOR(RAND() * 99999) AS random_numFROM numbers
WHERE "random_num" NOT IN (SELECT my_number FROM numbers)
LIMIT 1
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）