mysql 批量插入测试数据

创建数据库和数据表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

create database school_info;

use school_info;

DROP TABLE IF EXISTS `school`;
CREATE TABLE `school` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL COMMENT '学校名称',
  `status` tinyint DEFAULT '0' COMMENT '状态',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学校信息表';

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL COMMENT '班级名称',
  `school_id` bigint DEFAULT NULL COMMENT '学校id',
  `status` tinyint DEFAULT '0' COMMENT '状态',
  PRIMARY KEY (`id`),
  KEY `school_id` (`school_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='班级信息表';


DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL COMMENT '姓名',
  `sex` tinyint DEFAULT '0' COMMENT '性别',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  `class_id` bigint DEFAULT NULL COMMENT '班级',
  `status` tinyint DEFAULT '0' COMMENT '状态',
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生信息表';

创建存储过程(添加学校，班级，学生信息)

CREATE procedure addSchools()
BEGIN
    DECLARE i INT;
    SET i = 0;
    WHILE i<1000 DO
        INSERT INTO school(name,status) VALUES(concat('学校','_',i),1);
        SET i = i+1;
    END WHILE;
END

CREATE procedure addClasses()
BEGIN
    DECLARE i INT;
    SET i = 0;
    WHILE i<100000 DO
        INSERT INTO class(name,school_id,status) VALUES(concat('班级','_',i),floor(i/100) + 1,1);
        SET i = i+1;
    END WHILE;
END

CREATE procedure addStudents()
BEGIN
    DECLARE i INT;
    SET i = 0; 
    WHILE i<10000000 DO
        INSERT INTO student(name,sex,age,class_id,status) VALUES(concat('学生','_',i),FLOOR(RAND() * 2),FLOOR(RAND() * 50),floor(i/100)+1,1);
        SET i = i+1; 
    END WHILE; 
END

调用存储过程，生成数据

call addSchools();
call addClasses();
call addStudents();