mysql随机数函数

快来打我* 2023-09-25 09:09 24阅读 0赞

## MYSQL写入随机数语句 ##

declare@iint

select@i=count(\*)fromA

while@i>0

begin

UpdateAsetB=ceiling(rand()\*150+50)whereid=@i

set@i=@i-1

id是表A里的自增长列，不清楚你的表里有没有，若是没有的话，可以自己造个临时表，插入数据。

本回答由提问者推荐

![mysql随机数函数\_mysql随机数][mysql_mysql]

## mysql rand函数 怎么添加随机数进去 ##

UUID是一个由5位十六进制数的字符串表示的128比特数字，其格式为aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee：

前3个数字从一个时间戳产生。

第4个数字保持暂时唯一性，以防时间戳值失去单一性(例如,由于经济时)。

第5个数字是一个IEEE802节点号，它提供空间唯一性。若后者不可用，则用一个随机数字替换。(例如,由于主机没有以太网卡，或我们不知道怎样在你的操作系统上找到界面的机器地址)。假若这样，空间唯一性就不能得到保证。尽管如此，一个冲突的发生机率还是非常低的。

## mysql在类中每行填入一个随机数 ##

在mysql调用rand函数生成随机数，sql代码示例：

UPDATE \`表名\` SET \`字段名\`=ceiling(rand()\*500000+500000) WHERE (条件);mysql 取随机数

2010年04月26日 星期一 09:48

mysql 取随机数

\--对一个表取任意随机数

select \*

from tmp\_xf\_test

where id >= (select floor(rand() \* (select max(id) from tmp\_xf\_test)))

order by id limit 1;

\--有条件性的取随机数

select \*

from tmp\_xf\_test

where id >= (select floor(rand() \*

((select max(id) from tmp\_xf\_test where gid = 9) -

(select min(id) from tmp\_xf\_test where gid = 9))) +

(select min(id) from tmp\_xf\_test where gid = 9))

and gid = 9

order by id limit 1;

\--gid上存在索引

select \*

from tmp\_xf\_test as t1 join

(select round(rand() \* ((select max(id) from tmp\_xf\_test where gid = 9)-(select min(id) from tmp\_xf\_test where gid = 9))

\+(select min(id) from tmp\_xf\_test where gid = 9)) as id) as t2

where t1.id >= t2.id and t1.gid = 9

order by t1.id limit 1;

\#\#\#\#\#\#\#\#\#

不要用下面的杯具写法

mysql> insert into tmp\_xf\_test(user\_nick,gid,item\_id,gmt\_create,gmt\_modified,memo)

\-> select user\_nick,gid,item\_id,gmt\_create,gmt\_modified,memo from tmp\_xf\_test;

query ok, 165888 rows affected (9.65 sec)

records: 165888 duplicates: 0 warnings: 0

mysql> select \*

\-> from \`tmp\_xf\_test\`

\-> where id >= (select floor( max(id) \* rand()) from \`tmp\_xf\_test\` )

\-> order by id limit 1;

\+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

\+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

| 467 | 玄风|9 |123 | 2010-04-26 14:56:39 | 2010-04-26 14:56:39 | 玄风测试使用的数据 |

\+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

1 row in set (51.12 sec)

mysql> explain select \*

\-> from \`tmp\_xf\_test\`

\-> where id >= (select floor( max(id) \* rand()) from \`tmp\_xf\_test\` )

\-> order by id limit 1\\g

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type: primary

table: tmp\_xf\_test

type: index

possible\_keys: null

key: primary

key\_len: 8

ref: null

rows: 1

extra: using where

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 2

select\_type: uncacheable subquery

table: tmp\_xf\_test

type: index

possible\_keys: null

key: idx\_tmp\_xf\_test\_gid

key\_len: 4

ref: null

rows: 331954

extra: using index

2 rows in set (0.01 sec)

mysql> select \* from \`tmp\_xf\_test\` t1 join

\-> (select floor( max(id) \* rand()) as id from \`tmp\_xf\_test\` ) as t2

\-> where t1.id >=t2.id

\-> order by t1.id limit 1;

\+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

\+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

| 40311 | 玄风|9 |123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 | 40311 |

\+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

1 row in set (0.14 sec)

\#\#\#\#\#\#\#\#\#\#\#\#\#\#

mysql> select \* from \`tmp\_xf\_test\`

\-> where id >= (select floor(rand() \* (select max(id) from \`tmp\_xf\_test\`)))

\-> order by id limit 1;

\+------+-----------+-----+---------+---------------------+---------------------+--------------------+

\+------+-----------+-----+---------+---------------------+---------------------+--------------------+

| 1352 | 玄风|9 |123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 |

\+------+-----------+-----+---------+---------------------+---------------------+--------------------+

1 row in set (0.00 sec)

mysql> explain select \* from \`tmp\_xf\_test\`

\-> where id >= (select floor(rand() \* (select max(id) from \`tmp\_xf\_test\`)))

\-> order by id limit 1\\g

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type: primary

table: tmp\_xf\_test

type: index

possible\_keys: null

key: primary

key\_len: 8

ref: null

rows: 1

extra: using where

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 3

select\_type: subquery

table: null

type: null

possible\_keys: null

key: null

key\_len: null

ref: null

rows: null

extra: select tables optimized away

2 rows in set, 1 warning (0.00 sec)

对应的另外一种杯具写法是：

select \*

from tmp\_xf\_test

where id >= (select floor(rand() \* (max(id) - min(id))) + min(id) mid

from tmp\_xf\_test

where gid = 9)

and gid = 9 limit 1;

![mysql随机数函数\_mysql随机数\_02][mysql_mysql_02]

## MYSQL 获取随机数的问题！！！！！！！！！！！！！！！ ##

给你提供一个思路你看是否可行；

1.随机数不用MYSQL生成，用程序(如PHP)生成，范围在ID的最大和最小之间；

2.根据生成的随机数去数据库直接查询，如果没有就重复一次操作；mysql 取随机数

2010年04月26日 星期一 09:48

mysql 取随机数

\--对一个表取任意随机数

select \*

from tmp\_xf\_test

where id >= (select floor(rand() \* (select max(id) from tmp\_xf\_test)))

order by id limit 1;

\--有条件性的取随机数

select \*

from tmp\_xf\_test

where id >= (select floor(rand() \*

((select max(id) from tmp\_xf\_test where gid = 9) -

(select min(id) from tmp\_xf\_test where gid = 9))) +

(select min(id) from tmp\_xf\_test where gid = 9))

and gid = 9

order by id limit 1;

\--gid上存在索引

select \*

from tmp\_xf\_test as t1 join

(select round(rand() \* ((select max(id) from tmp\_xf\_test where gid = 9)-(select min(id) from tmp\_xf\_test where gid = 9))

\+(select min(id) from tmp\_xf\_test where gid = 9)) as id) as t2

where t1.id >= t2.id and t1.gid = 9

order by t1.id limit 1;

\#\#\#\#\#\#\#\#\#

不要用下面的杯具写法

mysql> insert into tmp\_xf\_test(user\_nick,gid,item\_id,gmt\_create,gmt\_modified,memo)

\-> select user\_nick,gid,item\_id,gmt\_create,gmt\_modified,memo from tmp\_xf\_test;

query ok, 165888 rows affected (9.65 sec)

records: 165888 duplicates: 0 warnings: 0

mysql> select \*

\-> from \`tmp\_xf\_test\`

\-> where id >= (select floor( max(id) \* rand()) from \`tmp\_xf\_test\` )

\-> order by id limit 1;

\+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

\+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

| 467 | 玄风|9 |123 | 2010-04-26 14:56:39 | 2010-04-26 14:56:39 | 玄风测试使用的数据 |

\+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

1 row in set (51.12 sec)

mysql> explain select \*

\-> from \`tmp\_xf\_test\`

\-> where id >= (select floor( max(id) \* rand()) from \`tmp\_xf\_test\` )

\-> order by id limit 1\\g

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type: primary

table: tmp\_xf\_test

type: index

possible\_keys: null

key: primary

key\_len: 8

ref: null

rows: 1

extra: using where

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 2

select\_type: uncacheable subquery

table: tmp\_xf\_test

type: index

possible\_keys: null

key: idx\_tmp\_xf\_test\_gid

key\_len: 4

ref: null

rows: 331954

extra: using index

2 rows in set (0.01 sec)

mysql> select \* from \`tmp\_xf\_test\` t1 join

\-> (select floor( max(id) \* rand()) as id from \`tmp\_xf\_test\` ) as t2

\-> where t1.id >=t2.id

\-> order by t1.id limit 1;

\+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

\+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

| 40311 | 玄风|9 |123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 | 40311 |

\+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

1 row in set (0.14 sec)

\#\#\#\#\#\#\#\#\#\#\#\#\#\#

mysql> select \* from \`tmp\_xf\_test\`

\-> where id >= (select floor(rand() \* (select max(id) from \`tmp\_xf\_test\`)))

\-> order by id limit 1;

\+------+-----------+-----+---------+---------------------+---------------------+--------------------+

\+------+-----------+-----+---------+---------------------+---------------------+--------------------+

| 1352 | 玄风|9 |123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 |

\+------+-----------+-----+---------+---------------------+---------------------+--------------------+

1 row in set (0.00 sec)

mysql> explain select \* from \`tmp\_xf\_test\`

\-> where id >= (select floor(rand() \* (select max(id) from \`tmp\_xf\_test\`)))

\-> order by id limit 1\\g

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type: primary

table: tmp\_xf\_test

type: index

possible\_keys: null

key: primary

key\_len: 8

ref: null

rows: 1

extra: using where

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 3

select\_type: subquery

table: null

type: null

possible\_keys: null

key: null

key\_len: null

ref: null

rows: null

extra: select tables optimized away

2 rows in set, 1 warning (0.00 sec)

对应的另外一种杯具写法是：

select \*

from tmp\_xf\_test

where id >= (select floor(rand() \* (max(id) - min(id))) + min(id) mid

from tmp\_xf\_test

where gid = 9)

and gid = 9 limit 1;

[mysql_mysql]: https://img2.baidu.com/it/u=3630152802,267698196&fm=26&fmt=auto
[mysql_mysql_02]: https://img1.baidu.com/it/u=3984101833,787443094&fm=26&fmt=auto

mysql随机数函数

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 python3 随机数函数

相关 mysql随机数函数

相关 mysql产生随机数

相关 mysql函数之获取随机数（rand，round，floor，ceiling函数）

相关 c++随机数函数rand()

相关 MySQL的随机数函数rand()的使用技巧

相关 C语言rand()函数产生随机数

相关 mysql 随机数

相关 MySQL生成随机数

相关我的随机数相关函数