Script Insert Thousands of Random Data into MySQL

When we create applications that use databases, of course, we need database simulations as in actual conditions.

With this simulation, we want to measure the performance of the application we are building. Both under ideal and extreme conditions.
In this article, Bardimin will share an easy way by using a script to insert thousands of random data into MySQL.

The first script we will share is a function from MySQL to generate random data.

While the second script is a procedure to insert data from the first script.

Before starting, you must first create a database, then create a table that we will insert with the data.

Create a random data table

Create a table with the name “ random_data ” which has 3 columns with names (id, column01, colum02)

CREATE TABLE `random_data` (
  `id` int(11) NOT NULL,
  `Column01` varchar(20) NOT NULL,
  `Column02` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

MySQL Random Data Script

1. Open phpMyAdmin. Then, in the left column, click on the database name. And in the right column, select the ” Routines ” tab . Then select it and click ” Add routine “.

2. Then create a function with the name “random_data” as shown in the following image.

3. For convenience , import the script below.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `random_string`(`length` SMALLINT(4) UNSIGNED) RETURNS varchar(20) CHARSET latin1
BEGIN
    SET @returnStr = '';
    SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    SET @i = 0;

    WHILE (@i < length) DO
        SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
        SET @i = @i + 1;
    END WHILE;

    RETURN @returnStr;
END$$
DELIMITER ;

4. This function will generate 20 characters of random data, change it according to your needs.

MySQL Data Insert Script

1. Repeat steps 1–3.
2. Then create a procedure with the name ” generate_random_string ” as shown in the following image.

3. Or import the following script

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_random_string`()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 10000 DO
    INSERT INTO random_data(column01,column02) VALUES (
    `random_string`(20),`random_string`(20)
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

4. The procedure will insert 10,000 rows of random data, change it according to what you need.

Running MySQL scripts

To run the script, on the “Routines” tab, select the “ generate_random_string ” script and click the “ Execute ” button.

The result will look like the following:

It’s easy to insert thousands of data in MySQL.

Hope it is useful.