百万级数据生成。

-- 用户表
CREATE TABLE `person` (
  `id` bigint(20) unsigned NOT NULL,
  `fname` varchar(100) NOT NULL,
  `lname` varchar(100) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `sex` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 用户部门表
CREATE TABLE `department` (
  `id` bigint(20) unsigned NOT NULL,
  `department` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 用户住址表
CREATE TABLE `address` (
  `id` bigint(20) unsigned NOT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

delimiter $$
drop procedure if exists generate;
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate`(IN num INT)
BEGIN

DECLARE chars VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

DECLARE fname VARCHAR(10) DEFAULT '';
DECLARE lname VARCHAR(25) DEFAULT '';
DECLARE id int UNSIGNED;
DECLARE len int;
set id=1;
WHILE id <= num DO
set len = FLOOR(1 + RAND()*10);
set fname = '';
WHILE len > 0 DO
SET fname = CONCAT(fname,substring(chars,FLOOR(1 + RAND()*62),1));
SET len = len - 1;
END WHILE;
set len = FLOOR(1+RAND()*25);
set lname = '';
WHILE len > 0 DO
SET lname = CONCAT(lname,SUBSTR(chars,FLOOR(1 + RAND()*62),1));
SET len = len - 1;
END WHILE;
INSERT into person VALUES (id,fname,lname, FLOOR(RAND()*100), FLOOR(RAND()*2));
set id = id + 1;
END WHILE;
END $$

delimiter $$
drop procedure if exists genDepAdd;
CREATE DEFINER=`root`@`localhost` PROCEDURE `genDepAdd`(IN num INT)
BEGIN

DECLARE chars VARCHAR(100) DEFAULT '行政技术研发财务人事开发公关推广营销咨询客服运营测试';
DECLARE chars2 VARCHAR(100) DEFAULT '北京上海青岛重庆成都安徽福建浙江杭州深圳温州内蒙古天津河北西安三期';

DECLARE depart VARCHAR(10) DEFAULT '';
DECLARE address VARCHAR(25) DEFAULT '';
DECLARE id int UNSIGNED;
DECLARE len int;
set id=1;
WHILE id <= num DO
set len = FLOOR(2 + RAND()*2);
set depart = '';
WHILE len > 0 DO
SET depart = CONCAT(depart,substring(chars,FLOOR(1 + RAND()*26),1));
SET len = len - 1;
END WHILE;
set depart=CONCAT(depart,'部');
set len = FLOOR(6+RAND()*18);
set address = '';
WHILE len > 0 DO
SET address = CONCAT(address,SUBSTR(chars2,FLOOR(1 + RAND()*33),1));
SET len = len - 1;
END WHILE;

INSERT into department VALUES (id,depart);
INSERT into address VALUES (id,address);
set id = id + 1;
END WHILE;
END $$

-- 关闭事务
set autocommit = 0;
-- 执行存储过程
call generate(1000000);
call genDepAdd(1000000);

-- 重启事务
set autocommit = 1;