CODEWITHSUNDEEP
mysqlsql
Speedychuck
Speedychuck

Reputation: 400

MYSQL INSERT with random values between 1 - 5

I want to do an INSER INTO statement but i need some fields to generate random numbers each time: Example 

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,RAND NUMBER,RAND NUMBER,...);

So each time it enters a random number into columns when creating a new record. SO most values will be static but some need to be RAND between a number set for example 1 -5 or 1000 - 5000.

Any help?

Upvotes: 1

Views: 8206

Answers (3)

Drew
Drew

Reputation: 24959

Insert some rows with random numbers from 1 to 5 in some columns.

Schema:

create table friday1
(   id int auto_increment primary key,
    value1 int not null,
    value2 int not null
);

Insert 3 rows:

insert friday1(value1,value2) select floor(rand()*5)+1,floor(rand()*5)+1;
insert friday1(value1,value2) select floor(rand()*5)+1,floor(rand()*5)+1;
insert friday1(value1,value2) select floor(rand()*5)+1,floor(rand()*5)+1;

Stored Proc helper:

drop procedure if exists insertMany;
DELIMITER $$
create procedure insertMany
(   howMany int
)
BEGIN
    DECLARE soFar int default 0;

    set howMany=least(howMany,500); -- max out at 500 regardless of IN parameter (RAND is slow)
    WHILE soFar<howMany DO
        insert friday1(value1,value2) select floor(rand()*5)+1,floor(rand()*5)+1;
        set soFar=soFar+1;
    END WHILE;
    select soFar; -- # of times in loop
END$$
DELIMITER ;

Test:

call insertMany(200);

Results:

select count(*) as theCount, 
least(min(value1),min(value2)) as theMin, 
greatest(max(value1),max(value2)) as theMax 
from friday1; 

+----------+--------+--------+
| theCount | theMin | theMax |
+----------+--------+--------+
|      203 |      1 |      5 |
+----------+--------+--------+

Upvotes: 1

Zoidburg
Zoidburg

Reputation: 159

Using this function should work:

FLOOR(min+RAND()*(max-min))

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,SELECT FLOOR(5+RAND()*(1-5)),SELECT FLOOR(5+RAND()*(1-5)),...);

Upvotes: 2

Alex Grogan
Alex Grogan

Reputation: 168

Take a look at this.

You can use RAND(), but as it says in the documentation,

If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

Hope this helps you

EDIT:

So if you want to use it in the INSERT INTO....i assume 

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, RAND(CAST( NEWID() AS varbinary )) * 5, RAND(CAST( NEWID() AS varbinary )) * 1000,...)

If this doesnt help, thumbs down as I'm going from what I'd try first and im not testing

Upvotes: 0

Related Questions