http://stackoverflow.com/questions/11042546/how-do-i-populate-a-mysql-table-with-many-random-numbers
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);
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