How to generate realistic test data in MySQL for QA environments

In software development, test data is crucial for performing quality checks in controlled environments without risking user privacy or compromising data security. Especially when handling sensitive data in database testing environments, it is essential to ensure that the data used does not reveal personally identifiable information (PII) or confidential details.

This article provides a technical approach to generating realistic and secure test data in MySQL, without the need for external tools. Through SQL and stored procedures, we can generate large volumes of realistic data to simulate test scenarios in QA databases. We also explain how to integrate anonymization techniques and ensure data confidentiality throughout the process.

Why is it important to generate realistic test data in MySQL?

QA and testing environments require databases that simulate production conditions, but without compromising user privacy. Real data, if not managed properly, can pose a risk to the security of the systems and individuals involved. Moreover, regulations like the GDPR (General Data Protection Regulation) mandate that personal data be handled with utmost care, especially in testing processes.

Generating realistic test data in MySQL allows QA teams, DBAs, and CIOs to conduct tests without compromising sensitive information privacy, ensuring that tests are as close to real-world conditions as possible while remaining compliant with legal standards.

Generating realistic data with MySQL

The following is a technical approach with MySQL scripts to create realistic test data for a QA testing environment. We will use MySQL’s native functions and anonymization techniques to ensure that the data is useful for testing but does not contain sensitive information.

Step 1: Create the database and table in MySQL

First, we need to create a test database in MySQL and the table that will contain the test data. In this case, we will simulate a user database for a registration system.

-- Crear la base de datos
CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

-- Crear la tabla de usuarios de prueba
CREATE TABLE test_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15),
    address VARCHAR(255),
    city VARCHAR(100),
    birthdate DATE
);

*Step 2: Generate realistic test data
*
Next, we’ll use a stored procedure in MySQL to generate random and realistic data. This script will generate data using MySQL’s built-in functions such as

RAND(), CONCAT(), FLOOR()

CREATE PROCEDURE generate_test_data(IN num_rows INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < num_rows DO
      INSERT INTO test_users (name, email, phone, address, city, birthdate)
      VALUES (
          CONCAT('User_', FLOOR(1 + (RAND() * 9999))),
          CONCAT('user', FLOOR(1 + (RAND() * 9999)), '@example.com'),
          CONCAT('+34 ', FLOOR(600000000 + (RAND() * 100000000))),
          CONCAT(FLOOR(1 + (RAND() * 999)), ' Fake Street'),
          CONCAT('City_', FLOOR(1 + (RAND() * 100))),
          DATE_SUB(CURDATE(), INTERVAL FLOOR(18 + (RAND() * 62)) YEAR)
      );
      SET i = i + 1;
  END WHILE;
END$$

Step 3: Execute the procedure to generate data

Once the procedure is created, we can generate the amount of data needed. For example, to generate 1,000 rows of test data, simply execute the following command:

CALL generate_test_data(1000);

This will insert 1,000 records of fake test data into the

test_users

Step 4: Verify the generated data

To verify that the data has been inserted correctly, we can perform a simple SQL query:

SELECT * FROM test_users LIMIT 10;

This will display the first 10 records, which will have realistic names, emails, phone numbers, and other details but will not be sensitive.

For advanced test data generation, anonymization, and data provisioning automation, explore Gigantics, our solution for secure and scalable test data provisioning.

🔗 Connect & Learn More
If you found this article useful, follow us on LinkedIn and visit our blog for more insights on data provisioning, test data management, and compliance.

Got questions? Leave a comment below! 🚀