1.随机生成测试数据
目标：创建一张表，插入随机的行数，生成测试所需的数据。
2.测试所需环境准备
库名：userdatabase
表名：test_user_table
| 字段 | 需求 |
|---|---|
| uname | 6字符随机长度 |
| ugender | 性别 M/F （M代表男，F代表女） |
| utel | 随机手机号 |
| ubirth | 随机出生日期（1980-2020）例如：1996-01-02 |
| uage | 根据出生日期算出来 |
| upassword | 12位随机密码（第一位是大写，剩下的是随机数字字母组合） |
3.使用方式
例如：插入数据100行 可以执行 call myp1_where(100);
说明：正常登录到mysql中，执行下面这段SQL语句，会生成相应的库和表还有字段名字，SQL语句可以重复执行，每次执行会清空原表内的数据，生成新的数据（默认插入100行）执行插入行数请修改括号内的数字即可。
CREATE DATABASE IF NOT EXISTS userdatabase;
USE userdatabase;
CREATE TABLE test_user_table (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(64) NOT NULL ,
ugender CHAR(1),
utel CHAR(11) NOT NULL,
ubirth DATETIME NOT NULL,
uage TINYINT NOT NULL,
upassword VARCHAR(32) NOT NULL
)ENGINE = INNODB CHARSET utf8mb4;
USE `userdatabase`;
DROP PROCEDURE IF EXISTS `test_user_table`.`myp1_where`;
TRUNCATE TABLE test_user_table;
DELIMITER $$
USE `test_user_table`$$
CREATE PROCEDURE `myp1_where`(IN num INT)
BEGIN
DECLARE str1 VARCHAR(64) DEFAULT 'abcdefghijklmnpqrstuvwxyz';
DECLARE str2 VARCHAR(10) DEFAULT 'MF';
DECLARE str3 VARCHAR(64) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE v_name,v_password,v_birth VARCHAR(64);
DECLARE v_tel CHAR(11);
DECLARE v_age TINYINT;
DECLARE v_gender CHAR(1);
DECLARE i INT DEFAULT 0;
DECLARE str,str12 VARCHAR(64);
WHILE i<num DO
SELECT SUBSTR(str1,1+FLOOR(RAND()*20),6) INTO v_name;
SELECT SUBSTR(str2,1+FLOOR(RAND()*2),1) INTO v_gender;
SELECT CONCAT('1',30+FLOOR(RAND()*70),LPAD(FLOOR(RAND()*100000000),8,'0')) INTO v_tel;
SELECT DATE(FROM_UNIXTIME(UNIX_TIMESTAMP('1970-01-01') +FLOOR(RAND() * (UNIX_TIMESTAMP('2020-11-14') - UNIX_TIMESTAMP('1970-01-01')+ 1)))) INTO v_birth;
SELECT YEAR(NOW())-YEAR(v_birth) INTO v_age;
#select 18+floor(rand()*12) into v_age;
SELECT REPLACE(UUID(),'-','') INTO str;
SELECT SUBSTR(str,FLOOR(RAND()*21+1),12) INTO str12;
SELECT CONCAT(SUBSTR(str3,FLOOR(RAND()*26+1),1),str12) INTO v_password;
INSERT INTO test_user_table(uname,ugender,utel,ubirth,uage,upassword) VALUES(v_name,v_gender,v_tel,v_birth,v_age,v_password);
SET i=i+1;
END WHILE ;
END$$
DELIMITER ;
CALL myp1_where(100);
SELECT * FROM test_user_table;
数据如下：
mysql> SELECT * FROM test_user_table;
+-----+--------+---------+-------------+---------------------+------+---------------+
| id | uname | ugender | utel | ubirth | uage | upassword |
+-----+--------+---------+-------------+---------------------+------+---------------+
| 1 | fghijk | M | 16082530768 | 2011-04-23 00:00:00 | 9 | M11eb96e2000c |
| 2 | npqrst | F | 16898658064 | 1985-02-06 00:00:00 | 35 | T211eb96e2000 |
| 3 | bcdefg | M | 19063733992 | 2000-10-04 00:00:00 | 20 | Tb81ccc25d211 |
| 4 | hijklm | F | 14291565933 | 1971-10-23 00:00:00 | 49 | B5d211eb96e20 |
| 5 | tuvwxy | F | 19821642349 | 1977-05-17 00:00:00 | 43 | Y0b8e19725d21 |
| 6 | jklmnp | F | 16789746817 | 2014-04-22 00:00:00 | 6 | S1eb96e2000c2 |
| 7 | klmnpq | M | 17705101254 | 1981-12-02 00:00:00 | 39 | K60b99c8a25d2 |