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:

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);

http://stackoverflow.com/questions/9927933/how-to-insert-random-numbers-mysql

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);

mysql dump triggers and stored procedures

I've been working on a little project to fix situations where we get incorrect definers on certain databases. The incorrect definer could be on an event, view, stored procedure, trigger, or a function. When fixing these problems it requires some effort because you cannot just edit a definer for triggers (even though you can for events and procedures). The trigger must be fully dropped and re-created with the new definer. To automate this task I've been writing a bash script to do the work.

Initially I had written a little bash function to DROP each function/procedure/trigger before restoring it. After writing the function I discovered that MySQL was automatically including a DROP IF EXISTS in the dump file for each stored procedure and each function. My bash function was redundant and not necessary. However, I wasn't seeing an equivalent DROP TRIGGER IF EXISTS command when I took a mysqldump of the triggers. After reading up on this, a lot of people were requesting this many years ago before I became a DBA: https://bugs.mysql.com/bug.php?id=34325

Lo and behold this feature was already in the version of MySQL I was running. I just needed to tell MySQL I wanted those statements included.

Here is the mysqldump command I've created for this task:

mysqldump -u user_name -p -h test_server.awesome.com --add-drop-trigger --databases my_test_db --no-create-info --no-data -R  > backup_triggers_routines.sql

Explanation of each option:
--add-drop-trigger  (this option tells it to add the DROP TRIGGER IF EXSTS syntax)
-- databases (this option allows me to specify more than one database, I'm only do one at a time)
--no-create-info (tell MySQL not to include CREATE TABLE info)
--no-data (tells MySQL not to include data because I only want the triggers/procs/functions)
-R (this is the same as --routines, tell it to also dump procedures/functions)

Note: Triggers are included by default but stored procedures and functions are not. If you wanted to you could include --triggers but it is not necessary.

If you wanted to dump only the routines (stored procedures/functions) you can use the --skip-triggers option to exclude triggers.

Seems silly that MySQL doesn't automatically include routines and then silly that it automatically includes the DROP syntax for routines but doesn't automatically include the DROP syntax for triggers.