Generate random name in MySQL - Stored Procedure

0

Good evening, is there any way to generate random names with stored procedures in MySQL? I have the following table:

  

ALUNO (id: int [PK], name (varchar (100)), age: int, Cr (float))

I need this table with 10mil entries popular. To generate the random numbers I found the RAND () function of MySQL, however I did not find anything related to random VARCHAR with Store Procedure.

    
asked by anonymous 20.05.2016 / 03:23

2 answers

0

One way I found it and nothing elegant was as follows.

Use the substring () function together with rand () to select a letter randomly from all of the alphabet I passed as an argument. After that use the concat () function to concatenate all the letters into one.

delimiter $$
drop procedure if exists projBD.populaAluno $$
create procedure populaAluno()
begin   
declare nome varchar(100);
declare idade int ;
declare cr float;
declare counter int default 0;
while counter <= 10000 do
    set nome = concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1),
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ ', rand()*28, 1)
            ) ;     
     set idade = floor(1 + (rand() * 99));
     set cr = rand()*10 ;
    insert into ALUNO (nome,idade,cr) 
    values (nome,idade,cr);
    set counter = counter + 1;
    end while;
End $$
delimiter ;
    
23.05.2016 / 19:33
0

Have you ever considered using a tool like link ? it can generate outputs in various formats, including SQL

    
20.05.2016 / 03:36