MySQL Test Data: Numbers, Names and Dates with SQL

To practise queries, or to test performance, you need rows. You can create thousands of them with SQL alone, using a number series and a few functions.

A number series with a recursive CTE

query.sql

SQL

SET SESSION cte_max_recursion_depth = 100000;

CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers
WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 1000)
SELECT n FROM seq;

SELECT COUNT(*) AS how_many, MIN(n) AS first_n, MAX(n) AS last_n FROM numbers;

Output

OUTPUT

+----------+---------+--------+
| how_many | first_n | last_n |
+----------+---------+--------+
|     1000 |       1 |   1000 |
+----------+---------+--------+
1 row in set

A recursive CTE builds rows step by step: start with 1, then keep adding 1 until 1000. It is explained in Recursive CTEs. Keep the resulting numbers table around: it is a handy helper for building test data.

Dates

query.sql

SQL

1
2
3

SELECT n, DATE_ADD('2026-01-01', INTERVAL n DAY) AS day
FROM numbers
WHERE n <= 5;

Output

OUTPUT

+---+------------+
| n | day        |
+---+------------+
| 1 | 2026-01-02 |
| 2 | 2026-01-03 |
| 3 | 2026-01-04 |
| 4 | 2026-01-05 |
| 5 | 2026-01-06 |
+---+------------+
5 rows in set

Varied values

The functions ELT() (pick the n-th item), MOD() and RAND() give variety. RAND(seed) with a fixed seed is repeatable, which makes test data reproducible:

query.sql

SQL

CREATE TABLE test_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    city VARCHAR(20) NOT NULL,
    amount DECIMAL(8,2) NOT NULL,
    status VARCHAR(10) NOT NULL,
    ordered_on DATE NOT NULL
);

INSERT INTO test_orders (customer_id, city, amount, status, ordered_on)
SELECT
    1 + MOD(n * 7, 100)                                      AS customer_id,
    ELT(1 + MOD(n, 4), 'Pune', 'Delhi', 'Kochi', 'Jaipur')   AS city,
    ROUND(50 + MOD(n * 37, 950) + MOD(n, 100) / 100, 2)      AS amount,
    ELT(1 + MOD(n, 3), 'paid', 'pending', 'cancelled')       AS status,
    DATE_ADD('2026-01-01', INTERVAL MOD(n * 3, 180) DAY)     AS ordered_on
FROM numbers;

SELECT COUNT(*) AS orders_created, MIN(ordered_on) AS earliest, MAX(ordered_on) AS latest FROM test_orders;

Output

OUTPUT

+----------------+------------+------------+
| orders_created | earliest   | latest     |
+----------------+------------+------------+
|           1000 | 2026-01-01 | 2026-06-27 |
+----------------+------------+------------+
1 row in set

Here MOD(n * 7, 100) makes a deterministic but spread-out pattern without RAND(). A fixed formula gives the same data every time, which is useful when you write examples and when you compare timings.

query.sql

SQL

SELECT city, status, COUNT(*) AS orders, ROUND(AVG(amount), 2) AS average_amount
FROM test_orders
GROUP BY city, status
ORDER BY city, status
LIMIT 6;

Output

OUTPUT

+--------+-----------+--------+----------------+
| city   | status    | orders | average_amount |
+--------+-----------+--------+----------------+
| Delhi  | cancelled |     83 |         515.39 |
| Delhi  | paid      |     83 |         526.05 |
| Delhi  | pending   |     84 |         531.35 |
| Jaipur | cancelled |     83 |         531.39 |
| Jaipur | paid      |     84 |         526.20 |
| Jaipur | pending   |     83 |         520.74 |
+--------+-----------+--------+----------------+
6 rows in set

Bigger tables

Join the number table with itself to multiply it:

query.sql

SQL

SELECT COUNT(*) AS cross_rows FROM numbers a JOIN numbers b ON b.n <= 100 WHERE a.n <= 100;

Output

OUTPUT

+------------+
| cross_rows |
+------------+
|      10000 |
+------------+
1 row in set

This query joined 100 rows with 100 rows and counted 10,000 combinations. Without the WHERE filter, 1000 x 1000 gives a million rows. Insert that many into a test table when you want to see how an index, a query plan or a slow query behaves on a table of realistic size. A query that is instant on 1,000 rows may crawl on a million, and you want to find that out before production.

Names and emails

query.sql

SQL

SELECT CONCAT(
         ELT(1 + MOD(n, 5), 'Asha', 'Ben', 'Chitra', 'Dev', 'Esha'), ' ',
         ELT(1 + MOD(n * 3, 5), 'Rao', 'Carter', 'Nair', 'Malhotra', 'Gupta')
       ) AS full_name,
       CONCAT('user', n, '@example.com') AS email
FROM numbers WHERE n <= 5;

Output

OUTPUT

+---------------+-------------------+
| full_name     | email             |
+---------------+-------------------+
| Ben Malhotra  | user1@example.com |
| Chitra Carter | user2@example.com |
| Dev Gupta     | user3@example.com |
| Esha Nair     | user4@example.com |
| Asha Rao      | user5@example.com |
+---------------+-------------------+
5 rows in set

Use example.com, example.org and example.net for fake emails: these domains are reserved for documentation and never deliver real mail.

Rules for test data

Never copy production personal data into a test database without anonymising it. Names, emails, phone numbers and addresses are personal data.
Make the data realistic in shape: similar number of rows, similar spread of values, similar text lengths. An index behaves differently when all values are the same.
Keep a script that rebuilds the data, so everyone has the same test set.
Use a separate database (shop_test), never the production one.

lightbulb

Reproducible beats random

A fixed formula or RAND(42) gives the same rows every run. When a test fails you can run it again and get the same failure.