Script Insert Thousands of Random Data into MySQL

When we create applications that use databases, of course, we need database simulations as in real 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)

script random data mysql 01
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 “.
TOP TUTORIALS:  Latest Huawei Indihome Modem Password
script random data mysql 02
  1. Then create a function with the name “ random_data ” as shown in the following image.
script random data mysql 03
  1. 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 ;
  1. 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.
script random data mysql 04
  1. 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 ;
  1. 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.

TOP TUTORIALS:  Using Filters in Excel
script random data mysql 05

The result will look like the following

script random data mysql 06

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

Hope it is useful.

Ads Blocker Image Powered by Code Help Pro
Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

I Have Disabled The AdBlock, Reload Now
Powered By
CHP Adblock Detector Plugin | Codehelppro