以下语句是进入 mysql 终端执行:
这一句的意思是改变终端的结束符号:
DELIMITER //
建表:
CREATE TABLE `person` (
`id` bigint(20) unsigned NOT NULL,
`username` varchar(100) NOT NULL,
`hobby` varchar(100) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`sex` tinyint(1) unsigned NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
创建生成数据procedure,num代表生成数据量的大小:
CREATE PROCEDURE `generate`(IN num INT)
BEGIN
DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE hobby_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE username VARCHAR(25) DEFAULT '';
DECLARE hobby VARCHAR(25) DEFAULT '';
DECLARE id int UNSIGNED;
DECLARE len int;
set id=1;
DELETE from person;
WHILE id <= num DO
set len = FLOOR(1 + RAND()*25);
set username = '';
set hobby = '';
WHILE len > 0 DO
SET username = CONCAT(username,substring(char_str,FLOOR(1 + RAND()*62),1));
SET hobby = CONCAT(hobby,substring(hobby_str,FLOOR(1 + RAND()*62),1));
SET len = len - 1;
END WHILE;
INSERT into person VALUES (id,username,hobby,FLOOR(RAND()*100), FLOOR(RAND()*2), now());
set id = id + 1;
END WHILE;
END
最后执行generate,生成100万数据
CALL generate(1000000);