Mysql 通过存储过程批量产生测试数据

1.删除之前的存储过程

DROP PROCEDURE IF EXISTS add_test_data;//删除存储过程add_test_data

2.创建存储过程

CREATE  PROCEDURE `add_test_data`(IN n int)
BEGIN  
       DECLARE i INT DEFAULT 1;
       DECLARE t INT DEFAULT 1;
       WHILE (i <= n ) DO
             set t = (1234567890 + FLOOR(1 + (RAND() * 300000000)));//生成随机时间戳
             INSERT INTO test_table VALUES(i,FLOOR(1 + (RAND() * 6)),t);
             set i=i+1;
       END WHILE;
END;

• 产生范围随机数

FLOOR(1 + (RAND() * 6))//产生1-6的随机数

3.调用存储过程

CALL add_test_data(100000);//生成10万条数据

示例

DROP PROCEDURE IF EXISTS add_test_data;
CREATE  PROCEDURE `add_test_data`(IN n int)
BEGIN  
       DECLARE i INT DEFAULT 1;
       DECLARE t INT DEFAULT 1;
       WHILE (i <= n ) DO
             set t = (1234567890 + FLOOR(1 + (RAND() * 300000000)));
             INSERT INTO test_table VALUES(i,FLOOR(1 + (RAND() * 6)),t);
             set i=i+1;
       END WHILE;
END;

CALL add_test_data(100000);