mysql添加大批量数据

1.创建存储过程函数

从上到下依次执行这些存储过程函数

#函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT) 
				RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN 
				DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
				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 ;

如果添加第一个函数时报错，请看和下面单词是否相关，如果相关执行set开头的语句。

select @@log_bin_trust_function_creators;

set GLOBAL log_bin_trust_function_creators = 1;

#函数2：创建随机数的函数
DELIMITER //
CREATE FUNCTION random_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 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 (random_num(10000, 10100), rand_string(6));
	UNTIL i = max_num
	END REPEAT;
	COMMIT; #提交事务
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 (random_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 stu_info (course_id, class_id, student_id, NAME ) 
	VALUES (random_num(10000, 10100), random_num(10000, 10200), random_num(1, 200000), rand_string(6));
	UNTIL i = max_num
	END REPEAT;
	COMMIT; #提交事务
END //
DELIMITER ;

2.创建表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for stu_info
-- ----------------------------
DROP TABLE IF EXISTS `stu_info`;
CREATE TABLE `stu_info`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NULL DEFAULT NULL,
  `class_id` int(11) NULL DEFAULT NULL,
  `student_id` int(11) NULL DEFAULT NULL,
  `NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1000001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NULL DEFAULT NULL,
  `course_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 101 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

3.执行添加数据的存储函数

添加100万条数据用时可能稍长，与电脑性能有关，本人电脑不到3分钟。

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