MySQL创建一个100万数据量的表
创建表
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`score` int(11) NOT NULL,
`create_time` timestamp NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
创建存储过程
CREATE DEFINER=`codingcloud`@`%` PROCEDURE `insert_person`()
begin
declare c_id integer default 1;
while c_id<=100000 do
insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
set c_id=c_id+1;
end while;
end
调用存储过程
CALL insert_person;
PS
执行比较耗时,大概一分钟1万条左右(依据机器性能)。做好充分心理准备