创建数据表

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL COMMENT '课程名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4;

DROP PROCEDURE IF EXISTS test_insert;
CREATE PROCEDURE test_insert()
BEGIN
    DECLARE i INT; // 也可以这样写 DECLARE i INT DEFAULT 1;
        SET i=1;
    WHILE i <= 100 DO
        INSERT INTO `course` (`name`) VALUES(CONCAT('课程',i));
        SET i=i+1;
    END WHILE;
END;
CALL test_insert();

DROP PROCEDURE IF EXISTS test_insert;
CREATE PROCEDURE test_insert()
BEGIN
    DECLARE x INT DEFAULT 1;
    DECLARE y INT DEFAULT 1;
    DECLARE z INT DEFAULT 1;
    WHILE z<=80000 DO
        INSERT INTO `student_score` (course_id,stu_id,score) VALUES(x,y,z);
        SET x=x+1;
                SET y=y+1;
                SET z=z+1;
                SET x = if(x > 101, 1, x+1);
                SET y = if(y > 80001, 1, y+1);
    END WHILE;
END;
CALL test_insert();

转载请注明出处 AE博客|墨渊 » mysql批量插入数据，sql语句批量插入，sql语句循环插入