Populate MySQL database tables with test data by parsing the SQL schema file.
Database table population without using any real or sensitive data:
Schema design and development:
+ Check table field population with specified datatypes, potential data truncation etc.
+ Test connection encoding and character encoding, and data insertion speeds.
Originally, I needed to populate a database containing 14 complex tables. Tools such as Spawner are ideal for populating small tables, but in this case, specifying the datatypes for 300+ fields to initiate Spawner would have been insanity.
Instead, why not parse the SQL schema?
The script expects the database schema to already exist in MySQL (`mysql -u root -p < test.sql`).
All table names and column names in the MySQL schema require back-ticks.
Unique keys must be removed from tables when the configuration array option random_data is set to false.
The majority of MySQL datatypes are supported.
Any foreign keys are disabled on data population.
Random character generation is slow in PHP, and such slowness further depends on field length, number of fields, and the number of rows being generated.
Multiple INSERTs are added in a single query, which is quite fast. The number of INSERTs per second will depend on MySQL configuration settings (the defaults are not optimised), datatype / length inserted, system load, operating system, hardware etc.
Configuration boolean toggles (
false by default):
+ make added integers incremental, enabling simplistic integer foreign keys.
+ populate a primary key field, e.g. a UUID used as a primary key (experimental, supports only some definitions).
Ensure the database already exists in MySQL
e.g. for the test schema:
mysql -u root -p < test.sql
Adjust the array connection details and parameters in the file databasefiller_example.php
Then execute this file with PHP on the command-line:
or run the file through a web server e.g.
SQLParser would have been used in this project had it been available in 2014.
Database Filler is released under the GPL v.3.