1、建表

CREATE TABLE user
(user_id varchar(36),name varchar(64),email varchar(64), password varchar(64),address varchar(255),mobile varchar(255),PRIMARY KEY(user_id)
)ENGINE=InnoDB;

2、分别构建一个rand_string函数和rand_mobile_or_email函数

set global log_bin_trust_function_creators = 1;DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGINDECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';DECLARE return_str varchar(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));SET i = i +1;END WHILE;RETURN return_str;
END $$
DELIMITER ;

set global log_bin_trust_function_creators = 1;DROP FUNCTION IF EXISTS rand_mobile_or_email;
DELIMITER $$
CREATE FUNCTION rand_mobile_or_email(n INT)
RETURNS VARCHAR(255)
BEGINDECLARE return_str varchar(255) DEFAULT '';CASE nWHEN 1 THENSET return_str = CONCAT(1881135,FLOOR(RAND()*10),FLOOR(RAND()*10),FLOOR(RAND()*10),FLOOR(RAND()*10));ELSESET return_str = CONCAT(CONCAT(1881135,FLOOR(RAND()*10),FLOOR(RAND()*10),FLOOR(RAND()*10),FLOOR(RAND()*10)),'@163.com');END CASE;RETURN return_str;
END $$
DELIMITER ;

3、创建存储过程

DELIMITER $$DROP PROCEDURE IF EXISTS `insert_user`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(IN item INTEGER)BEGINDECLARE counter INT;SET counter = item;WHILE counter >= 1 DOinsert into user (user_id,name,email,password,address,mobile)values(uuid(),rand_string(8),rand_mobile_or_email(2), rand_string(8),rand_string(8),rand_mobile_or_email(1));SET counter = counter - 1;END WHILE;END$$
DELIMITER ;

4、调用

call insert_user(100);