尝试使用
stored procedure(用期望的行数替换1000,用测试年代替2014,也见
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();
修改您的需求.删除程序:
DROP PROCEDURE generate_data;
也许这可以给你一个开始!