How to Populate MySQL Table with Random Data

Often we need to test our SQL queries against sample test data. It can be tiresome to manually create large tables with random data. Did you know that you can write SQL queries to populate table with random data that you can use for testing? In this article, we will look at a couple of simple methods to populate MySQL table with random data.

How to Populate MySQL Table with Random Data

We will look at a couple of ways to populate MySQL table with random data.

1. For Small-Medium Tables

If you want to create a small-medium sized table with random information in it, you can use the following query to create an empty table first.

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

and the following query to populate it with random data.

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(1000, 2000, 5000);

The above query creates a stored procedure called InsertRand which takes 3 parameters – no. of rows, starting number and ending number. We finally call the stored procedure to insert 1000 rows with random numbers between 2000 and 5000. It basically runs a while loop with number of iterations equal to number of specified rows. It uses RAND() MySQL function to get a random floating point number between 0 and 1 and multiple it with the floor and ceiling values specified in stored procedure call.

2. For Large Tables

If you want to create a table with say 100k rows, then you can use the following query to create an empty table.

CREATE TABLE your_table (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, val int);

and the next query to populate it.

DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
  DECLARE i INT DEFAULT 100;

  WHILE i < 100000 DO
    INSERT INTO your_table (val) VALUES (i);
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL prepare_data();

It simply loops from 100 to 100k and inserts a new row for each integer. You can customize it as per your requirement, to use more columns, or skip some records. This can become a little slow if you try to insert millions of rows. In such cases, it might be helpful to bulk insert thousands of rows at one go.

In this short article, we have learnt how to populate large table in MySQL with random data for testing.

How to Populate MySQL Table with Random Data