为了学习验证高性能mysql,自动生成大量的数据做测试。内容来源于网络。
delimiter $$ create definer=`root`@`%` function `random_num`( ) returns int(5) begin declare i int default 0; set i = floor(100+rand()*10); return i; end$$ delimiter ;
delimiter $$ create definer=`root`@`%` function `random_string`(n int) returns varchar(255) charset latin1 begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzabcdefjhijklmnopqrstuvwxyz'; 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()*52),1)); set i = i + 1; end while; return return_str; end$$ delimiter ;
delimiter $$ create definer=`root`@`%` function `random_string_phone`(n int) returns varchar(255) charset latin1 begin declare chars_str varchar(100) default '1234567890'; 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()*10),1)); set i = i + 1; end while; return return_str; end$$ delimiter ;
create table `sys_user_myisam` ( `user_id` bigint(100) not null auto_increment, `username` varchar(100) default null comment '用户名', `password` varchar(100) default null comment '密码', `salt` varchar(100) default null comment '盐', `email` varchar(100) default null comment '邮箱', `mobile` varchar(100) default null comment '手机号', `status` int(1) default '1' comment '状态 0:禁用 1:正常', primary key (`user_id`) ) engine=myisam auto_increment=1000001 default charset=utf8 comment='系统用户myisam';
delimiter $$ create definer=`root`@`%` procedure `insert_sys_user_myisam`(in start int(10),in max_num int(10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into sys_user_myisam (user_id,username,password,salt,email,mobile,status) values (start+i,random_string(10),random_string(6),random_string(10),random_string(20),random_string_phone(12),1); until i = max_num end repeat; commit; end$$ delimiter ;
create table sys_user_innodb engine=myisam auto_increment=1000001 default charset=utf8 as select * from sys_user_myisam;
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论