一、利用临时表实现动态插入数据

DELIMITER $$
CREATE PROCEDURE insert_data_sp(id_begin INT,id_end INT)
BEGIN

DECLARE i INT;

#如果存在该表，删除该表
DROP TABLE IF EXISTS patient_info;

#创建表
CREATE TABLE IF NOT EXISTS patient_info(
brid00 VARCHAR(50) PRIMARY KEY COMMENT '用户唯一识别码',
brxm00 VARCHAR(30) NOT NULL COMMENT '用户姓名',
brlxdh VARCHAR(30) COMMENT '用户电话号码',
sex VARCHAR(30) COMMENT '性别',
certype VARCHAR(30) COMMENT '证件类型',
certid VARCHAR(30) COMMENT '证件号码',
birth CHAR(8) COMMENT '出生日期'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#创建新的临时表
DROP TEMPORARY TABLE IF EXISTS test_demo.a_temp;
CREATE TEMPORARY TABLE IF NOT EXISTS test_demo.a_temp(
c_id VARCHAR(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#往临时表插入一条原始数据
INSERT INTO test_demo.a_temp VALUES('1');

SET i = id_begin;
WHILE(i<=id_end) DO
		UPDATE test_demo.a_temp SET c_id = i;
		INSERT into test_demo.patient_info select c_id as brid00,'用户','18750133555','男','身份证','350122199903033819','19990303' from test_demo.a_temp;
		IF MOD(i,100000)=0 THEN
		   COMMIT;
		END IF;
		SET i=i+1;
END WHILE;
COMMIT;
SET autocommit =1;
END;

2、 使用字符串拼接实现动态插入数据

DELIMITER $$
CREATE PROCEDURE insert_data_new(id_begin INT,id_end INT)
BEGIN
DECLARE str_tmp LONGTEXT;
DECLARE i INT;

DROP TABLE IF EXISTS patient_info;
CREATE TABLE IF NOT EXISTS patient_info(
brid00 VARCHAR(50) PRIMARY KEY COMMENT '用户唯一识别码',
brxm00 VARCHAR(30) NOT NULL COMMENT '用户姓名',
brlxdh VARCHAR(30) COMMENT '用户电话号码',
sex VARCHAR(30) COMMENT '性别',
certype VARCHAR(30) COMMENT '证件类型',
certid VARCHAR(30) COMMENT '证件号码',
birth CHAR(8) COMMENT '出生日期'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET i = id_begin;
SET str_tmp = '';
WHILE(i<=id_end) DO
    SET str_tmp = concat(str_tmp,'(',i,',','\'用户\'',',','\'18750133555\'',',','\'男\'',',','\'身份证\'',',','\'350122199903153819\'',',','\'19990315\'',')',';');
		#SET str_tmp = SUBSTRING_INDEX(str_tmp,',',LENGTH(str_tmp)-LENGTH(REPLACE(str_tmp,',','')));
    SET @insert_sql_str = CONCAT("INSERT INTO test_demo.patient_info VALUES",str_tmp);
		PREPARE insert_sql_str FROM @insert_sql_str;
    EXECUTE insert_sql_str;
    DEALLOCATE PREPARE insert_sql_str;
    IF MOD(i,500000)=0 THEN
		   COMMIT;
 		END IF;
		SET i=i+1;
		SET str_tmp = '';
END WHILE;
COMMIT;
END;

3、 仅主键不能重复追求执行效率批量插入数据

DELIMITER $$
DROP PROCEDURE  IF EXISTS insert_data_sp;
CREATE PROCEDURE insert_data_sp(id_begin INT,id_end INT)
BEGIN

DECLARE i INT;
DECLARE j INT;

DROP TABLE IF EXISTS patient_info;
CREATE TABLE IF NOT EXISTS patient_info(
brid00 INTEGER PRIMARY KEY AUTO_INCREMENT COMMENT '用户唯一识别码',
brxm00 VARCHAR(30) NOT NULL COMMENT '用户姓名',
brlxdh VARCHAR(30) COMMENT '用户电话号码',
sex VARCHAR(30) COMMENT '性别',
certype VARCHAR(30) COMMENT '证件类型',
certid VARCHAR(30) COMMENT '证件号码',
birth CHAR(8) COMMENT '出生日期'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET i =1;

WHILE(i<=20000) DO
INSERT into test_demo.patient_info(brxm00,brlxdh,sex,certype,certid,birth)VALUES ('用户','18750133555','男','身份证','350122199903033819','19990303');
SET i = i+1;
END WHILE;
COMMIT;

SET j = id_begin;
WHILE(j<id_end) DO
		INSERT into test_demo.patient_info(brxm00,brlxdh,sex,certype,certid,birth) select brxm00,brlxdh,sex,certype,certid,birth from test_demo.patient_info limit 20000;
		SET j = j+1;
END WHILE;
COMMIT;
END;

#调用存储过程
call insert_data_sp(1,5000)