Tuesday, March 15, 2016

Generating random data in MySQL

I've had to do this a number of times and found the ability to create random data in tables with a stored procedure very useful. Here are a couple of my favorite examples of doing this:


CREATE TABLE rand_numbers (
    number INT NOT NULL
) ENGINE = InnoDB;

DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT, IN MinVal INT, IN MaxVal INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            INSERT INTO rand_numbers VALUES (MinVal + CEIL(RAND() * (MaxVal - MinVal)));
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER ;

CALL InsertRand(1111, 2222, 5555);


I made a couple spelling corrections.

CREATE TABLE foo (
    bar INT NOT NULL
) ENGINE = InnoDB;

DELIMITER $$
CREATE PROCEDURE random_fill( IN cnt INT )
BEGIN

    fold: LOOP

         IF cnt < 1 THEN
             LEAVE fold;
         END IF;

        INSERT INTO foo ( bar ) VALUES ( 9 + CEIL( RAND() * 90 ) );

        SET cnt = cnt - 1;
    END LOOP fold;

END$$   
DELIMITER ;

CALL random_fill(10000);

Here is an example I've made:

CREATE TABLE `random` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `unique` varchar(255) NOT NULL DEFAULT '',
  `date_action_performed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `data` text NOT NULL,
  `type` enum('blue','green','yellow') NOT NULL DEFAULT 'blue',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;



DROP PROCEDURE IF EXISTS `random_fill_fun`;


DELIMITER $$
CREATE PROCEDURE random_fill_fun( IN cnt INT)
BEGIN

    fold: LOOP

         IF cnt < 1 THEN
             LEAVE fold;
         END IF;

        SET @MIN_DATE = '2016-03-01 00:00:00';
        SET @MAX_DATE = '2016-03-23 23:59:59';

        INSERT INTO `random` (`id`, `unique`, `date_action_performed`, `user_id`, `data`, `type`) VALUES (NULL,  UUID(), TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN_DATE, @MAX_DATE)), @MIN_DATE), 9 + CEIL( RAND() * 90 ), SUBSTRING(MD5(RAND()) FROM 1 FOR 50), ELT(0.5 + RAND() * 3, 'blue', 'green', 'yellow'));


        SET cnt = cnt - 1;
    END LOOP fold;

END$$  
DELIMITER ;



CALL random_fill_fun(10000);



No comments:

Post a Comment