插入100w数据测试

insert_load_test.sql

drop table if exists optlog1;

CREATE TABLE `optlog1` (
  `object` varchar(255) DEFAULT NULL,
  `objectId` int(11) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `log` varchar(255) DEFAULT NULL,
  `cabinet` varchar(255) DEFAULT NULL,
  `org` int(11) DEFAULT NULL,
  `uid` varchar(255) DEFAULT NULL,
  `isDeleted` tinyint(1) DEFAULT NULL,
  `userIp` varchar(255) DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `updatedBy` int(11) DEFAULT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `createdBy` (`createdBy`),
  KEY `updatedBy` (`updatedBy`)
) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8;

drop procedure if exists load_foo_test_data;
delimiter #
create procedure load_foo_test_data()
begin

declare v_max int unsigned default 1000000;
declare v_counter int unsigned default 0;

  truncate table  `optlog1`;
  start transaction;
  while v_counter < v_max do
    INSERT INTO `optlog1`
(`object`,
`objectId`,
`action`,
`log`,
`cabinet`,
`org`,
`uid`,
`isDeleted`,
`userIp`,
`createdBy`,
`updatedBy`,
`createdAt`,
`updatedAt`)
VALUES
('application',
100000,
'application',
'application',
'1000',
1234567890,
'12345678901234567890',
0,
'fff:192.168.111.1111',
1234567890,
1234567890,
now(),
now());

    set v_counter=v_counter+1;
  end while;
  commit;
end #

delimiter ;

call load_foo_test_data();