mysql随机数函数

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 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;

+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

| id | user_nick | gid | item_id | gmt_create| gmt_modified| memo|

+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

| 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;

+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

| id| user_nick | gid | item_id | gmt_create| gmt_modified| memo| id|

+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

| 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;

+------+-----------+-----+---------+---------------------+---------------------+--------------------+

| id| user_nick | gid | item_id | gmt_create| gmt_modified| memo|

+------+-----------+-----+---------+---------------------+---------------------+--------------------+

| 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;文章来源地址https://www.toymoban.com/news/detail-410050.html

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;

+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

| id | user_nick | gid | item_id | gmt_create| gmt_modified| memo|

+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

| 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;

+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

| id| user_nick | gid | item_id | gmt_create| gmt_modified| memo| id|

+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+

| 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;

+------+-----------+-----+---------+---------------------+---------------------+--------------------+

| id| user_nick | gid | item_id | gmt_create| gmt_modified| memo|

+------+-----------+-----+---------+---------------------+---------------------+--------------------+

| 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;