Generating Random Data with MySQL

Once you have your new database schema ready, you sometimes wish to insert random data just to see how things are going. For example, to test the performance of specific SELECT or INSERT statements or to help you decide better on the index usage. Some may be rendered useless or excessive by such a process or you may discover that e.g. a composite index is better suited for your needs.

Moreover, you could decide on the storage engine you should use (although for this there are many other parameters which involve application requirements as well, for instance the need for transactional logic makes imperative the use of InnoDB) or get a rough estimate of the disk space your DB will consume over time. One could even use this technique combined with the mysqlslap utility to test your server configuration, although it can generate test data itself.

So let’s take it step-by-step.

Create 2 sample tables, one “holding data” and a second one which will serve as a lookup table:

As you can see, I use the LPAD function along with RAND functions. Easy to guess, we will be using the RAND function quite frequently in this example; it returns a random floating-point number between 0 and 1, thus we must use CAST to change the returned value to a string type for MD5. Of course are character set will be constrained by the hexadecimal representation produced by ​​MD5.

Now that we have a single random record in our table, we can start adding a lot more. The proper tool for this task is the INSERT INTO … SELECT statement.