MySQL: create random data text strings

To generate a random text string:
  select conv(floor(rand() * 99999999999999), 20, 36) ;
To create a table that we will fill with random data:
  CREATE TABLE `foos` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` char(20),
    PRIMARY KEY (`id`)
  );
To create a procedure to generate random names and insert these:
  delimiter $$  
  create procedure randomizer()
    begin
      declare i int Default 0 ;
      declare random char(20) ;
      myloop: loop
      set random=conv(floor(rand() * 99999999999999), 20, 36) ;
      insert into `foos` (`id`, `name`) VALUES (i+1,random) ;
      set i=i+1;
      if i=5 then
        leave myloop;
	end if;
    end loop myloop;
  end $$
  delimiter ;
To show the results:
  select * from foos;
  +-----+--------------+
  | id  | name         |
  +-----+--------------+
  |   1 | 3E8JA1YVG2YU | 
  |   2 | 5SDK0UT446R9 | 
  |   3 | 2DFPC6C8ZF1  | 
  |   4 | 1CD04T10L8OY | 
  |   5 | MF0XNYHHP0C  | 
  +-----+--------------+



What's Next?

blog comments powered by Disqus