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)
插入数据成功。