Search code examples
mysqlrandominsert

What is error? i'm trying to fill table with random values

I have two similar tables:

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB;

CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB;

I want to fill both tables with random values:

drop procedure if exists random_records;
truncate table t1;
truncate table t2;
delimiter $$

create procedure random_records(n int)
begin
set @i=1;
set @m=100000;
while @i <= n do
    insert into t1(c1,c2) values(rand()*@m,rand()*@m);
    insert into t2(c1,c2) values(rand()*@m,rand()*@m);
   set @i=@i+1;
end while;
end $$

delimiter ;

call random_records(100);
select * from t1 limit 10;
select * from t2 limit 10;
select count(*) from t1;
select count(*) from t2;

Here is what i see in table t1: Below is what i see in table t1:

I don't understand why there is a lot of '0' and'1' Function count() returns 210 for t1 and 208 for t2 - one mystery more

Solution

  • The most likely reason for the presence of many zeros and ones in the c1 and c2 columns of both tables is that the rand() function is returning very small numbers. This is because the @m variable, which is used to scale the random numbers generated by rand(), is set to a relatively low value of 100,000.

    As a result, the random numbers generated are mostly between 0 and 0.00001, which is why you are seeing many zeros and ones in the tables. To fix this, you can increase the value of @m to a higher number, such as 1,000,000 or even 10,000,000, to generate larger random numbers.

    As for the discrepancy in the number of rows in the two tables, it is likely due to the fact that the insert statements in the random_records procedure are not being executed atomically.

    This means that there is a chance that one of the insert statements could fail, resulting in fewer rows being inserted into one of the tables. To fix this, you can wrap the insert statements in a transaction to ensure that they are executed as a single unit of work.

    For example, you can modify the random_records procedure as follows:

    drop procedure if exists random_records;
truncate table t1;
truncate table t2;
delimiter $$

create procedure random_records(n int)
begin
set @i=1;
set @m=1000000;

start transaction;

while @i <= n do
    insert into t1(c1,c2) values(rand()*@m,rand()*@m);
    insert into t2(c1,c2) values(rand()*@m,rand()*@m);
   set @i=@i+1;
end while;

commit;
end $$

delimiter ;

    This should ensure that the insert statements are executed atomically and that the number of rows in both tables is consistent.