头歌-关系数据库系统的查询优化

第1关：快速批量插入数据

任务描述

本关任务：掌握快速往表中插入大量数据。

参考代码

#请在此编写SQL语句创建表并插入数据

create TABLE if NOT EXISTS student

(

    student_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    score_math INT NOT NULL,

    score_eng INT NOT NULL,

    score_art INT NOT NULL

)ENGINE=INNODB DEFAULT CHARSET =UTF8;

DROP PROCEDURE IF EXISTS mytest;

DELIMITER $$

CREATE PROCEDURE mytest()

BEGIN

DECLARE N INT DEFAULT 1;

SET @exesql = 'insert into student

(score_math,score_eng,score_art) values(88,77,90)';

SET @exedata = '';

WHILE n < 10000 DO

SET @exedata = CONCAT(@exedata,',','(79,86,98)');

SET @exesql=CONCAT(@exesql,@exedata);

SET @exedata = '';

SET n=n + 1;

END WHILE;

SET @exesgl=CONCAT(@exesql,";");

PREPARE stmt FROM @exesql;

EXECUTE stmt;

DEALLOCATE PREPARE Stmt;

END;$$

CALL mytest();