案例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;