如何用随机数据生成1000000行? [英] How to generate 1000000 rows with random data?

问题描述

背景

我正在开发某种数据记录器.

Im working on some kind of data logger.

我想尝试1000000(1M)行需要多少存储空间，以及Raspberry Pi如何处理如此大的表.我想通过分组，计算平均值和其他性能实验来运行一些查询.

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`)
)

问题

如何在MySQL中用1亿行填充它?

How can I fill it with 1000000 million rows in MySQL?

要求:

• data.datetime字段:随机时间戳记，但仅从一年起
• data.value字段:给定范围内的随机浮点数(例如0.00-100.00)
• data.id是自动递增的，无需担心
• data.channel始终为1，也无需在意这一点

• data.datetime field: random timestamps but only from one year
• data.value field: random float numbers from given range (0.00-100.00 for example)
• data.id is autoincrement, no need to care about that
• data.channel is always 1, no need to care about that too

我对SQL有一点了解，但是我在 PL/SQL中并不擅长，在SQL中循环等等.

I know SQL a bit, but I'm not good in PL/SQL, loops in SQL etc.

为了清楚起见-我使用的是MySQL 5.5.

To make it clear - im using MySQL 5.5.

提到PL/SQL是我的错误，我认为PL/通常代表SQL中的过程功能，而不仅仅是Oracle.

Mentioned PL/SQL was my mistake, I thought PL/ stands for procedural features in SQL in general, not just Oracle.

推荐答案

尝试使用存储过程(将1000替换为所需的行数，将2014替换为测试年份，另请参见

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!

这篇关于如何用随机数据生成1000000行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！