MySQL test data generator

MySQL-flavoured output

Set the SQL export's dialect to mysql and identifiers are wrapped in backticks rather than double quotes — which matters, because MySQL only treats double quotes as identifier quoting under ANSI_QUOTES, and by default reads them as string literals:

CREATE TABLE `customers` (
  `id` INT,
  `full_name` VARCHAR(100),
  `email` VARCHAR(120),
  `signup_date` DATE
);

INSERT INTO `customers` (`id`, `full_name`, `email`, `signup_date`) VALUES
  (1, 'Elena Rossi', 'elena.rossi7@example.org', '2024-03-18'),
  (2, 'Marco Schneider', 'marco_schneider@example.com', '2024-07-02');

Batching packs 250 rows into each statement, which is the difference between a seed script that runs in seconds and one that runs in minutes. Keep an eye on max_allowed_packet if you widen the schema considerably.

Bulk loading with LOAD DATA

Past a few thousand rows, export CSV instead:

LOAD DATA LOCAL INFILE 'customers.csv'
INTO TABLE customers
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Two things commonly block this. LOCAL requires local_infile enabled on both server and client (mysql --local-infile=1), and without LOCAL the file must sit inside secure_file_priv on the server. Check both before assuming the export is at fault.

utf8mb4, not utf8

Generated data includes non-ASCII names and cities by default, and the export is UTF-8. If your table or connection uses MySQL's legacy utf8 — three bytes, no astral plane — anything outside the basic multilingual plane is silently mangled or rejected. Use utf8mb4 for the column, the table and the connection. The generator's Emoji field is the fastest possible test of whether you actually did.

NULL in a CSV load

MySQL's LOAD DATA does not treat an empty field as NULL — it stores an empty string, or zero in a numeric column. Fields set to blank via Blank % therefore need explicit handling:

LOAD DATA LOCAL INFILE 'customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 ROWS
(id, full_name, @email, signup_date)
SET email = NULLIF(@email, '');

Use the SQL export instead if you would rather not think about it — it writes NULL literals directly.

AUTO_INCREMENT after loading

Inserting explicit primary keys leaves the auto-increment counter behind, so the next application insert collides. Reset it once the load finishes:

ALTER TABLE customers AUTO_INCREMENT = 100001;

Or truncate before loading — TRUNCATE TABLE resets the counter, DELETE FROM does not, which is a distinction that has cost many people an afternoon.

Speeding up a large load

SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- load here
SET foreign_key_checks = 1;
SET unique_checks = 1;
COMMIT;

Disabling checks is appropriate for a development seed where you trust the generated data, and inappropriate anywhere near production. Add secondary indexes after loading rather than before, and remember that InnoDB inserts fastest in primary-key order — sequential Row Number keys are the friendly case.

Strict mode is your friend

If a load silently truncates a long value or turns a bad date into 0000-00-00, strict mode is off. Turn it on in development so the generated data tells you about column-width and format mismatches instead of quietly absorbing them — that is precisely the feedback a test fixture exists to give.

Common questions

Related

• PostgreSQL test data — COPY, sequences and deferred constraints.
• MongoDB test data — document fixtures via mongoimport.
• Database test data guide — constraints, nulls and foreign keys.
• CSV export reference — quoting and delimiters.