建表如下:create table ttrnd(a int not null, b int not null, c int not null, d int not null);
希望在a, b, c, d这四个字段中插入1-n范围内的数字若干行,每行每个字段间的数字各不相同,是随机出现的。如下表类似我想要的:
3,1, 2, 4
2,7, 5, 9
...
希望在a, b, c, d这四个字段中插入1-n范围内的数字若干行,每行每个字段间的数字各不相同,是随机出现的。如下表类似我想要的:
3,1, 2, 4
2,7, 5, 9
...
方法二:准备一个表 t(id) 存放从 1,2,3,...,N,然后 select id from t order by rand() limit 4 ,然后插入
select round(rand()*N) n是你要的。
ceil () /ceiling() 向上取整
floor () 向下取整
来达到你的要求
not null);
Query OK, 0 rows affected (0.08 sec)mysql> delimiter ;
mysql>
mysql> delimiter //
mysql> create procedure createRecord(N int)
-> begin
-> declare r1,r2,r3,r4 int;
-> set r1=1+rand()*N;
->
-> REPEAT
-> set r2=1+rand()*N;
-> UNTIL r2 != r1 END REPEAT;
->
-> REPEAT
-> set r3=1+rand()*N;
-> UNTIL r3 != r1 and r3 != r2 END REPEAT;
->
-> REPEAT
-> set r4=1+rand()*N;
-> UNTIL r4 != r1 and r4 != r2 and r4 != r3 END REPEAT;
->
-> insert into ttrnd values (r1,r2,r3,r4);
-> end
-> //
Query OK, 0 rows affected (0.34 sec)mysql> delimiter ;
mysql> call createRecord(10);
Query OK, 1 row affected (0.08 sec)mysql> select * from ttrnd;
+---+----+----+---+
| a | b | c | d |
+---+----+----+---+
| 6 | 10 | 11 | 3 |
+---+----+----+---+
1 row in set (0.00 sec)mysql> call createRecord(10);
Query OK, 1 row affected (0.06 sec)mysql> select * from ttrnd;
+---+----+----+---+
| a | b | c | d |
+---+----+----+---+
| 6 | 10 | 11 | 3 |
| 2 | 11 | 7 | 6 |
+---+----+----+---+
2 rows in set (0.00 sec)mysql>
mysql> select id from t1 where id<10 order by rand();
+----+
| id |
+----+
| 9 |
| 5 |
| 6 |
| 4 |
| 1 |
| 3 |
| 2 |
| 8 |
| 7 |
+----+
9 rows in set (0.02 sec)
通过下面语句可得4个随机不重复值。
set @rownum=0;
select max(if(r=1,id,0)) as r1,
max(if(r=2,id,0)) as r2,
max(if(r=3,id,0)) as r3,
max(if(r=4,id,0)) as r4
from (
select @rownum:=@rownum+1 as r,id from (
select id from t1 where id<10 order by rand() limit 4
) t
) kmysql> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)mysql> select max(if(r=1,id,0)) as r1,
-> max(if(r=2,id,0)) as r2,
-> max(if(r=3,id,0)) as r3,
-> max(if(r=4,id,0)) as r4
-> from (
-> select @rownum:=@rownum+1 as r,id from (
-> select id from t1 where id<10 order by rand() limit 4
-> ) t
-> ) k;
+------+------+------+------+
| r1 | r2 | r3 | r4 |
+------+------+------+------+
| 3 | 1 | 5 | 4 |
+------+------+------+------+
1 row in set (0.00 sec)mysql> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)mysql> select max(if(r=1,id,0)) as r1,
-> max(if(r=2,id,0)) as r2,
-> max(if(r=3,id,0)) as r3,
-> max(if(r=4,id,0)) as r4
-> from (
-> select @rownum:=@rownum+1 as r,id from (
-> select id from t1 where id<10 order by rand() limit 4
-> ) t
-> ) k;
+------+------+------+------+
| r1 | r2 | r3 | r4 |
+------+------+------+------+
| 5 | 6 | 3 | 2 |
+------+------+------+------+
1 row in set (0.00 sec)mysql>
如果随机数的应精确到小数点后3位,可以考虑下面的写法:
select floor(round(rand(),3)*n);
如果随机数的应精确到小数点后3位,可以考虑下面的写法:
select floor(round(rand(),3)*n);
To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement: 因此floor(rand()*n)最大值可以为n-1。
SELECT FLOOR(7 + (RAND() * 5));