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 |
+—————+———————+———————————-+————+
No comments