Mysql大量插入随机数据方法--存储过程

案例1:

创建测试表：

mysql> create table bigdata (id int,name char(2));

创建存储过程：
mysql> delimiter //
mysql> create procedure rand_data(in num int)
-> begin
-> declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; --总共62个字符。
-> declare str2 char(2);
-> declare i int default 0;
-> while i<num do
-> set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
-> set i=i+1;
-> insert into bigdata values (floor(rand()*num),str2);
-> end while;
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

插入一百万条数据：

mysql> call rand_data(1000000);
  Query OK, 1 row affected (1 hour 11 min 34.95 sec)

mysql> select * from bigdata limit 300,10;
+--------+------+
| id     | name |
+--------+------+
| 230085 | WR   |
| 184410 | 7n   |
| 540545 | nN   |
| 264578 | Tf   |
| 571507 | at   |
| 577023 | 0M   |
| 731172 | 7h   |
| 914168 | ph   |
| 391848 | h6   |
| 665301 | dj   |
+--------+------+
10 rows in set (0.00 sec)

常用的几个函数:

concat(x, y, z): 生成字符进行相加连接
floor(10) : 生成随机生成小于10的整数
rand() : 生成随机生成0-1之间的浮点数
now() : 生成当前日期和时间
随机访问10-200 的数字: floor(10 + rand() *200)

案例二:

CREATE TABLE `vote_record_memory` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `user_id` VARCHAR(20) NOT NULL,
   `vote_id` INT(11) NOT NULL,
   `group_id` INT(11) NOT NULL,
   `create_time` datetime NOT NULL,
   PRIMARY KEY (`id`),
   KEY `index_id` (`user_id`) USING HASH
) ENGINE = memory auto_increment=1 default charset=utf8;

CREATE TABLE `vote_record` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `user_id` VARCHAR(20) NOT NULL,
   `vote_id` INT(11) NOT NULL,
   `group_id` INT(11) NOT NULL,
   `create_time` datetime NOT NULL,
   PRIMARY KEY (`id`),
   KEY `index_user_id` (`user_id`)  USING HASH
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

-- 修改结束符
delimiter //

-- 创建function
CREATE FUNCTION rand_string(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE  chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str  varchar(255) DEFAULT '';
DECLARE i INT  DEFAULT 0;
WHILE i<n  DO
SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND()*62),1));
SET  i = i+1;
END WHILE;
RETURN return_str;
END //

delimiter ;
CALL add_vote_memory(1000000);

INSERT into vote_record SELECT * from vote_record_memory;
select count(*) from vote_record;