求各位大佬帮忙如何建立一张一百万数据的表

Question

use test;

drop table if EXISTS dept;
create table dept (
 deptno MEDIUMINT UNSIGNED not null DEFAULT 0,
  dname varchar(20) default '',
 ioc varchar(13) default''
) ENGINE = myisam default CHARSET ='utf8';
ALTER table dept add PRIMARY key(deptno);

drop table if EXISTS emp;
CREATE TABLE `emp` (
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `ename` varchar(20) NOT NULL DEFAULT '',
  `job` varchar(9) NOT NULL DEFAULT '',
  `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',
  `hiredate` date NOT NULL COMMENT '入职日期',
  `salary` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '红利',
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '部门编号'
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

DROP function if EXISTS rand_string;
#创建一个指定字符个数的函数
delimiter //
create function rand_string(n INT)
#返回字符串，注意：此处关键字是returns 而不是return
returns varchar(255)

BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    # 定义返回结果字符串
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i int DEFAULT 0;
    WHILE i < n DO
        set return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        set i=i+1;
    END while;
    RETURN return_str;
END ;//
delimiter ;

drop function if exists rand_num;
delimiter //
CREATE function rand_num()
returns int(5)
BEGIN
 DECLARE i int default 0;
 set i = FLOOR(10+RAND()*500);
 return i;
END ;//
delimiter ;

drop PROCEDURE IF EXISTS insert_emp ;
delimiter //
CREATE PROCEDURE insert_emp (in start_no int(10),in max_num int(10))

BEGIN
 DECLARE i int default 0;
 # 设置自动提交为false
 set autocommit =0;
 # 开启循环
 REPEAT
     set i = i+1;
     insert into emp values((start_no+i),rand_string(6),'SALESMAN',0001,CURDATE(),rand_num(),400,0002);

 UNTIL i=max_num
 END REPEAT;
END ;//
delimiter ;

call insert_emp(10000,10000000);

小弟想这样做一张一百万数据的表 可是最后总是显示call insert_emp(10000,10000000) Error Code: 2013. Lost connection to MySQL server during query 30.001 sec

max_allowed_packet都已经设置成1G了 请问如何建立？

Answer 1

这个跟max_allowed_packet没关吧，是你执行set autocommit =0;后没有commit超时了吧

Answer 2

mysql的配置 wait_timeout 改一下

show variables like '%timeout';