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

创建测试表：

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)

 

插入数据成功。