mysql 数据生成_MySQL生成大量测试数据方法

Mysql创建测试大量测试数据

修改mysql配置

max_heap_table_size=4000M

innodb_flush_log_at_trx_commit=0

创建测试数据库

create database helloJiu charset=utf8;

use helloJiu;

创建数据表

CREATE TABLE `test_innodb` (

`id` INT (11) NOT NULL AUTO_INCREMENT,

`user_id` VARCHAR (20) NOT NULL,

`group_id` INT (11) NOT NULL,

`create_time` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `index_user_id` (`user_id`) USING HASH

) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

创建随机字符串函数

delimiter $$

CREATE FUNCTION rand_string(n int) RETURNS varchar(255)

begin

declare chars_str varchar(100)

default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";

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()*62),1));

set i= i+1;

end while;

return return_str;

end $$

delimiter ;

创建存储过程

delimiter $$

CREATE PROCEDURE `insert_data`(IN n int)

BEGIN

DECLARE i INT DEFAULT 1;

WHILE (i <= n ) DO

INSERT into test_innodb (user_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 100) ,now() );

set i=i+1;

END WHILE;

END $$

delimiter ;

调用存储过程, 插入数据

call insert_data(1000000);

插入成功后, 可以快速创建其他数据表并插入大量数据

create table test_myisam engine=myisam as select * from test_innodb;