mysql8批量插入百万级别测试数据，用于sql效率测试

--创建学生信息表

CREATE TABLE `student_info` 
(
  `id` int (11) AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

--创建课程表

CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`course_id` int NOT NULL,
`course_name` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

--主从函数配置

set GLOBAL log_bin_trust_function_creators =1; 

--创建随机数函数

DELIMITER //
CREATE FUNCTION `rand_num`(from_num INT ,to_num INT) RETURNS int (11)
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i; 
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
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 PROCEDURE `insert_course`( max_num INT )
BEGIN 
DECLARE i INT DEFAULT 0; 
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6)); 
UNTIL i = max_num 
END REPEAT; 
COMMIT; #提交事务
END //
DELIMITER ;

--创建插入学生信息表的存储过程

DELIMITER //
CREATE PROCEDURE `insert_stu`( max_num INT )
BEGIN 
DECLARE i INT DEFAULT 0; 
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student_info (course_id, class_id ,student_id ,name ) VALUES (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6)); 
UNTIL i = max_num 
END REPEAT; 
COMMIT; #提交事务
END //
DELIMITER ;

--执行存储过程：100门课、1000000学生

CALL insert_course(100);
CALL insert_stu(1000000);