CREATE DATABASE demo04; -- 创建数据库
USE demo04; -- 使用数据库
CREATE TABLE tb_user -- 创建数据表
(
id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NULL DEFAULT NULL,
age TINYINT NOT NULL DEFAULT 0,
sex VARCHAR(1) NOT NULL
)ENGINE=innodb DEFAULT CHARSET=utf8mb4;
DESC tb_user;
TRUNCATE tb_user;
-- 创建存储过程
DROP PROCEDURE IF EXISTS proc_test;
DELIMITER $
CREATE PROCEDURE proc_test(IN num BIGINT) -- num为传入参数,指定要插入的条目数
BEGIN
-- username由var1和var2中分别随机取一个字符组成
DECLARE var1 VARCHAR(26) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE var2 VARCHAR(10) DEFAULT'0123456789';
DECLARE i BIGINT DEFAULT 1;
DECLARE usename VARCHAR(20) DEFAULT '';
DECLARE usesex VARCHAR(1) DEFAULT '';
DECLARE useage TINYINT DEFAULT 0;
WHILE i <= num DO
IF i % 2 = 0
THEN SET usesex='男';
ELSE SET usesex='女' ;
END IF;
SELECT CONCAT(SUBSTR(var1, FLOOR(RAND()*26+1),1),SUBSTR(var2, FLOOR(RAND()*10+1),1)) INTO usename;
SELECT FLOOR(RAND()*26+1) INTO useage;
INSERT INTO tb_user(id,name,age,sex) VALUES (i,usename,useage,usesex);
SET i = i+1;
END WHILE;
END$
DELIMITER ;
CALL proc_test(1000000);
SELECT COUNT(*) FROM tb_user;