MYSQL INSERT with random values between 1 - 5

Question

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?

[This link](http://www.techonthenet.com/mysql/functions/rand.php) shows you how to use `RAND()` to generate a random number within a range. You can just plug this into your `INSERT` statement. — Tim Biegeleisen, Jul 22 '16 at 14:30
I tried that so in the values I have VALUES ('511222', RAND()*(5-1)+1, '323632') Because I want numbers between 1 and 5. using MYSQL work bench not sure if that makes a difference — Speedychuck, Jul 22 '16 at 14:31

Zoidburg · Answer 1 · 2016-07-22T14:59:50.737

2

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)),...);

edited Jul 22 '16 at 14:59

answered Jul 22 '16 at 14:31

Zoidburg

159
8

How would this look in the insert statement? @Zoidburg – Speedychuck Jul 22 '16 at 14:34
Just tried this: getting a 1582 incorrect parameter count in call to native function floor. – Speedychuck Jul 22 '16 at 14:46
1

Need an extra `)` after `(10-100)` – apokryfos Jul 22 '16 at 14:48
Ok this worked, however its inserting as 0 I have (1-5) should it not be between 1 and 5 that is generated? – Speedychuck Jul 22 '16 at 14:54
Ok its because the db only accepts numbers as strings grrr! I need to populate that field with a number between 1 and 5 which is entered as a string how can I fix this? – Speedychuck Jul 22 '16 at 14:57
Do you mean something like this `CAST(FLOOR(5+RAND()*(1-5)) AS CHAR)` – Zoidburg Jul 22 '16 at 15:04
This would work, however, just noticed it is set to TINYINT so how do I get around this? Thanks for help by way. – Speedychuck Jul 22 '16 at 15:16
So the column only accepts TINYINT and the default expression is set to '2' SO how do I get CAST(FLOOR(5+RAND()*(1-5)) AS CHAR) to add as a TINYINT? – Speedychuck Jul 22 '16 at 15:44

score 1 · Answer 2 · answered Jul 22 '16 at 15:01

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

Alex Grogan · Answer 3 · 2016-07-22T14:46:09.000

0

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

edited Jul 22 '16 at 14:46

answered Jul 22 '16 at 14:38

Alex Grogan

158
1
1
11

Need to know how to use it in an INSERT INTO :( so when I use the insert it will Generate the number between 1 and 5 for that column – Speedychuck Jul 22 '16 at 14:41
Look at my edit, try it out....youll need to round the number if you want a whole number – Alex Grogan Jul 22 '16 at 14:46
CAST(FLOOR(5+RAND()*(1-5)) AS CHAR) Now works, however when I check what the column accepts it is a TINYINT. So FLOOR RAND generates the number between 1 and 5 but wont insert it into the column becasue its trying to add an INT which is then converted into a String. How do I add as TINYINT is my issues. @Alex Grogan – Speedychuck Jul 22 '16 at 16:05
Do you really want to use a TINYINT? It only has a range of 0-255? You're betting off changing the design of the table so its an INT instead – Alex Grogan Jul 25 '16 at 15:56

MYSQL INSERT with random values between 1 - 5

3 Answers3

Linked