How to generate 1000000 rows with random data?
Solution 1
Try it with a stored procedure (replace 1000
with desired amount of rows, and 2014
with test year, also see generate random timestamps in mysql)
CREATE TABLE `data`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`datetime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`channel` int(11) DEFAULT NULL,
`value` float DEFAULT NULL,
PRIMARY KEY (`id`)
);
DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000 DO
INSERT INTO `data` (`datetime`,`value`,`channel`) VALUES (
FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
ROUND(RAND()*100,2),
1
);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL generate_data();
Modify to your needs. To delete the procedure:
DROP PROCEDURE generate_data;
Maybe this can give you a start!
Solution 2
We have MySQL Random Data Generator - easy to use procedure for generating the random data internally from mysql itself.
Related videos on Youtube
![Kamil](https://i.stack.imgur.com/lorbp.gif?s=256&g=1)
Kamil
Electrican by education Electronics engineer by hobby Programmer (some languages professionally, some by hobby) Windows Server admin - few years of experience Sorry for my grammar and spelling errors, feel free to correct them by editing my posts.
Updated on September 15, 2022Comments
-
Kamil almost 2 years
Background
Im working on some kind of data logger.
I want to try how much storage space I need for 1000000 (1M) rows and how Raspberry Pi deals with such big table. I want to run some queries with grouping, calculating averages and other performance experiments.
My table looks like this:
CREATE TABLE `data` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `datetime` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `channel` int(11) DEFAULT NULL, `value` float DEFAULT NULL, PRIMARY KEY (`id`) )
Question
How can I fill it with 1000000 million rows in MySQL?
Requirements:
data.datetime
field: random timestamps but only from one yeardata.value
field: random float numbers from given range (0.00-100.00 for example)data.id
is autoincrement, no need to care about thatdata.channel
is always 1, no need to care about that too
I know SQL a bit, but I'm not good in
PL/SQL,loops in SQL etc.EDIT:
To make it clear - im using MySQL 5.5.
Mentioned PL/SQL was my mistake, I thought PL/ stands for procedural features in SQL in general, not just Oracle.
-
jmmeier almost 10 yearsWhy solve this problem with only sql? Use a simple scripting language like Perl or Python and insert data as you wish.
-
Ashalynd almost 10 years
-
Ken White almost 10 yearsPL/SQL is for Oracle, but your tag says MySQL. They're nowhere near the same thing, and they are in no way compatible. What specifically are you asking about?