mysql存储函数和存储过程批量插入测试数据

1.使用函数设置

 show variables like 'log_bin_trust_function_creators';

 set global log_bin_trust_function_creators=1;

2.生成定长的指定汉字

DROP FUNCTION if exists rand_name;



delimiter //

create function `rand_name`(n int) returns varchar(255) charset utf8

begin

    declare char_str varchar(255) default '哈章与张霞笑小欧由于语塔堆太湖想笑一二三四五张李孙刘六七八九十宇宇你是最耀在夺中我人貌夲厅蝇眼的星再干一杯永远生命不过短暂的烟火爱你所爱无问西东国中城梦回圧圧田另温处理穿上佣震黑灯瞎火夺';

    declare return_name varchar(255) default '';

    declare i int default 0;

    while i < n do

        set return_name = concat(return_name, substring(char_str, floor(1+RAND()*100),1));

        set i = i + 1;

    end while;

    return return_name;

END //

delimiter ;



SELECT rand_name(3);

3.生成年龄

DROP FUNCTION if EXISTS get_age;

delimiter //

CREATE FUNCTION get_age()

RETURNS INT

BEGIN

  RETURN  1+FLOOR(RAND()*150);

END //

delimiter ;



select get_age();

4.生成手机号

DROP FUNCTION if EXISTS phone_head;

delimiter //

create  function `phone_head`() returns char(3) charset utf8

begin

    declare head char(3);

    declare bodys varchar(225) DEFAULT '130 131 132 133 134 135 136 137 138 139 186 187 189 151 157';

    declare starts int;

    set starts = 1 + floor(rand()*15)*4 ;

    set head =trim(substring(bodys,starts,3));

    return head;

END //

delimiter ;




DROP FUNCTION rand_number_string;

delimiter //

create function `rand_number_string`(n int) returns varchar(255) charset utf8

begin

    declare char_str varchar(255) default '0123456789';

    declare return_str varchar(255) default '';

    declare i int default 0;

    while i < n do

        set return_str=concat(return_str,substring(char_str,floor(1+rand()*10),1));

        set i=i+1;

    end while;

    return return_str;

end //

delimiter ;




DROP FUNCTION get_phone;

delimiter //

create  function `get_phone`() returns varchar(20) charset utf8

begin

    declare phone varchar(20);

    set phone = trim(concat(phone_head(),rand_number_string(8)));

    return phone;

end //

delimiter ;

5.生成随机过去100年的某个时间

DROP FUNCTION if exists get_date;

delimiter //

CREATE FUNCTION get_date()

RETURNS DATETIME

BEGIN

  DECLARE random_seconds BIGINT DEFAULT  1+FLOOR(RAND()*3153600000);

  RETURN date_sub(NOW(),INTERVAL random_seconds SECOND);

END //

delimiter ;

6.生成姓名

DROP FUNCTION  get_real_name;

delimiter //

CREATE FUNCTION get_real_name()

returns varchar(255) charset utf8

BEGIN

  DECLARE last_name VARCHAR(255) DEFAULT '赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚';

  DECLARE first_name VARCHAR(255) DEFAULT '夫君子之行静以修身俭以养德非淡泊无以明志非宁静无以致远夫学须静也才须学也非学无以广才非志无以成学淫慢则不能励精险躁则不能治性年与时驰意与日去遂成枯落多不接世悲守穷庐将复何及';

  RETURN concat(substring(last_name,floor(1+190*rand()),1),substring(first_name,floor(1+84*RAND()),2));

END //

delimiter ;

 7.生成邮箱

delimiter //

CREATE FUNCTION get_email()

RETURNS VARCHAR(50)

BEGIN

   RETURN CONCAT(rand_number_string(9),"@qq.com");

END //

delimiter ;

8.生成随机字符串

drop function if exists rand_string;
delimiter //

create function rand_string(n int) returns varchar(255)

begin

    declare chars_str varchar(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

    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 ;

 9.生成用户

DROP PROCEDURE generate_user;

delimiter //

CREATE PROCEDURE generate_user(IN num INT)

BEGIN

  DECLARE i INT DEFAULT 0;

  while i< num do

   INSERT INTO user(NAME,age,jobnumber,phone,email,nick_name,create_time) VALUES(get_real_name(),get_age(),rand_number_string(5),get_phone(),get_email(),rand_string(8),get_date());

   SET i =i +1;

  END while;

END //

delimiter ;

10.表结构

DROP TABLE IF EXISTS `user`;

CREATE TABLE IF NOT EXISTS `user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `jobnumber` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `nick_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

  `create_time` datetime DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

11.生成100w用户

CALL generate_user(1000000);