I needed a quick-and-dirty table of random data yesterday, and threw together these sql statements to achieve that.

Here’s the table that needs random data.

CREATE TABLE test.mytable (
integer_field int(11) DEFAULT NULL,
date_field datetime NOT NULL,
string_field varchar(255) DEFAULT NULL,
enum_field enum(‘dogs’,’cats’,’giraffes’) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Set time variables for use below.

SET @MIN = ‘2016-01-01 00:00:00’;
SET @MAX = ‘2016-06-30 23:59:59’;

Manipulate some random values to insert data.

insert into test.mytable
(
integer_field,
date_field,
string_field,
enum_field
)
values
(
FLOOR(RAND() * 10000),
TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN),
SUBSTRING(MD5(RAND()) FROM 1 FOR 50),
ELT(0.5 + RAND() * 3, ‘dogs’, ‘cats’, ‘giraffes’)
);

Can use a bash script to feed multiple rows.

for n in {1..10000}; do mysql -e”SET @MIN = ‘2016-01-01 00:00:00’;SET @MAX = ‘2016-06-30 23:59:59’;insert into test.mytable ( integer_field, date_field, string_field, enum_field ) values  ( FLOOR(RAND() * 10000), TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN),  SUBSTRING(MD5(RAND()) FROM 1 FOR 50),  ELT(0.5 + RAND() * 3, ‘dogs’, ‘cats’, ‘giraffes’) );”; done

Sample the data.

MariaDB [(none)]> select * from test.mytable limit 7000,15;
+—————+———————+———————————-+————+
| integer_field | date_field          | string_field                     | enum_field |
+—————+———————+———————————-+————+
|          7434 | 2016-06-07 12:16:17 | 20c96fd35f500d0e9d7ce31d7947fb68 | dogs       |
|          1062 | 2016-01-15 14:19:52 | b2a4522db9c226af7ef76723eb2138d1 | dogs       |
|           680 | 2016-06-22 07:05:22 | c814fc13888fa835877d525fe06dd596 | giraffes   |
|          1545 | 2016-06-28 06:10:08 | 0f8434c39b93dac467c9f7aef49faa73 | cats       |
|          7608 | 2016-03-11 10:06:43 | c46c15f9450ca2a559b0bfb7af26f296 | dogs       |
|          5516 | 2016-02-28 17:22:37 | cf156c001c072366d739d8f7d7f5b242 | giraffes   |
|          3110 | 2016-01-31 10:28:45 | 2f37c9d1c0d2dfeddbbc96c61f31ee5c | dogs       |
|          7133 | 2016-05-21 08:57:05 | d3e361ba15592bb1a68c0447f4e7389b | cats       |
|          7242 | 2016-03-11 05:56:10 | 95e891946128bcb3d25271ce1c32307b | cats       |
|          1071 | 2016-05-20 02:30:29 | d2d9e2869e684e3beee8eb67bdeeb3de | dogs       |
|          6202 | 2016-05-21 16:29:46 | 01e1117c511d029ce2146cbfd4fb1b24 | giraffes   |
|           961 | 2016-04-05 11:32:26 | 97e285476750bed37cbca77629ef7c94 | dogs       |
|          7788 | 2016-06-02 06:30:42 | 728feff92cdef22d90a7a551b2898f1b | giraffes   |
|          8710 | 2016-03-17 14:33:41 | d33f26efc2eba3d2861e7132b66d23e1 | dogs       |
|          4733 | 2016-02-15 20:17:07 | 2ae11a1259de08c824de10422782bfc0 | cats       |
+—————+———————+———————————-+————+

(Originally published at: https://www.pythian.com/blog/quick-random-data-mysql/.)