Mysql插入千万临时数据（存储过程）

最新推荐文章于 2022-02-18 10:18:36 发布

cl1180

最新推荐文章于 2022-02-18 10:18:36 发布

阅读量961

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/cl1180/article/details/122554632

版权

表结构：

CREATE TABLE `t_emp` (
  `empno` int(11) NOT NULL AUTO_INCREMENT,
  `empname` varchar(20) DEFAULT NULL,
  `login_time` datetime DEFAULT NULL,
  `login_node` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_emp_1` (
  `empno` int(11) NOT NULL AUTO_INCREMENT,
  `empname` varchar(20) DEFAULT NULL,
  `login_time` datetime DEFAULT NULL,
  `login_node` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_emp_2` (
  `empno` int(11) NOT NULL AUTO_INCREMENT,
  `empname` varchar(20) DEFAULT NULL,
  `login_time` datetime DEFAULT NULL,
  `login_node` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

单表插入1千万数据

CREATE DEFINER=`root`@`172.17.0.1` PROCEDURE `createData`( )
BEGIN
DECLARE i INT;
DECLARE j INT;
DECLARE nameNo INT;
SET i = 0;
WHILE i < 10000 DO
	set j = 0;
	set nameNo = i*1000;
	-- 开启事务
	START TRANSACTION;
	WHILE j < 1000 DO
		INSERT INTO `t_emp`(`empname`, `login_time`, `login_node`) VALUES (CONCAT('jhon',nameNo), NOW(), 'WX');
		SET j = j + 1;
		set nameNo = nameNo + 1;
	END WHILE;
	-- 提交事务
	COMMIT;
	SET i = i + 1;
END WHILE;

END

分表插入1千万数据

CREATE DEFINER=`root`@`172.17.0.1` PROCEDURE `createData`( )
BEGIN
DECLARE i INT;
DECLARE j INT;
DECLARE nameNo INT;
DECLARE tableName VARCHAR(100);
DECLARE tableNo INT;
SET tableNo = 1;
WHILE tableNo <= 2 DO
	-- 表名拼接，循环每张表，插入数据
	SET tableName = CONCAT("t_emp_",tableNo);
	SET i = 0;
	-- 每张表插入的数据总量：i*1000
	WHILE i < 1 DO
		set j = 0;
		set nameNo = i*1000;
		-- 每插入1000条提交一次事务
		START TRANSACTION;
		WHILE j < 1000 DO
			-- 拼接插入语句
			set @sqlTmp = CONCAT('INSERT INTO ',tableName,' (`empname`, `login_time`, `login_node`) VALUES ("john',nameNo,'","',NOW(),'","WX")');
			PREPARE stmt FROM @sqlTmp;
			-- 执行sql
			EXECUTE stmt;
			SET j = j + 1;
			set nameNo = nameNo + 1;
		END WHILE;
		-- 提交事务
		COMMIT;
		SET i = i + 1;
	END WHILE;
	set tableNo = tableNo + 1;
END WHILE;

END