MySQL中的RAND()函数使用详解

这篇文章主要介绍了MySQL中的RAND()函数使用详解,是MySQL入门学习中的基础知识,需要的朋友可以参考下

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

---