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.

TOP TUTORIALS:  How to merge all Excel Sheets into one Sheet

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

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:  How to Remove Arrows on Shortcut Icons in Windows 11
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.
CREATE DEFINER=`root`@`localhost` FUNCTION `random_string`(`length` SMALLINT(4) UNSIGNED) RETURNS varchar(20) CHARSET latin1
    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;

    RETURN @returnStr;
  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.
TOP TUTORIALS:  Real-Time Application Dashboard with Yii2, Node.js, MySQL and Chart.js
script random data mysql 04
  1. Or import the following script
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_random_string`()
  WHILE i < 10000 DO
    INSERT INTO random_data(column01,column02) VALUES (
    SET i = i + 1;
  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.

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.