參考:
http://blog.itpub.net/29254281/viewspace-1686302/

準備環境
1.建立數字輔助表
create table nums(id int not null primary key);

delimiter $$
create procedure pFastCreateNums(cnt int)
begin
declare s int default 1;
truncate table nums;
insert into nums select s;
while s*2<=cnt do
insert into nums select id+s from nums;
set s=s*2;
end while;
end $$
delimiter ;

call pFastCreateNums(1000000);

數字輔助表的行數決定最後能生成的錶行數的最大值.

2.建立生成隨機字元的函式

DROP FUNCTION IF EXISTS rand_string;
delimiter //
CREATE FUNCTION rand_string(l_num int UNSIGNED,l_type tinyint UNSIGNED)
RETURNS varchar(2000)
BEGIN
-- Function : rand_string
-- Author : dbachina#dbachina.com
-- Date : 2010/5/30
-- l_num : The length of random string
-- l_type: The string type
-- 1.0-9
-- 2.a-z
-- 3.A-Z
-- 4.a-zA-Z
-- 5.0-9a-zA-Z
-- :
-- mysql> select rand_string(12,5) random_string;
-- +---------------+
-- | random_string |
-- +---------------+
-- | 3KzGJCUJUplw |
-- +---------------+
-- 1 row in set (0.00 sec)
DECLARE i int UNSIGNED DEFAULT 0;
DECLARE v_chars varchar(64) DEFAULT '0123456789';
DECLARE result varchar (2000) DEFAULT '';
IF l_type = 1 THEN
SET v_chars = '0123456789';
ELSEIF l_type = 2 THEN
SET v_chars = 'abcdefghijklmnopqrstuvwxyz';
ELSEIF l_type = 3 THEN
SET v_chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
ELSEIF l_type = 4 THEN
SET v_chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
ELSEIF l_type = 5 THEN
SET v_chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
ELSE
SET v_chars = '0123456789';
END IF;
WHILE i < l_num DO
SET result = concat( result,substr(v_chars,ceil(rand()*(length(v_chars)-1)),1) );
SET i = i + 1;
END WHILE;
RETURN result;
END;
//
delimiter ;

準備實驗表.
先建立一些帶有外來鍵約束的表.資料庫名稱是 probe

CREATE TABLE `t_jvm_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`app_name` varchar(32) NOT NULL COMMENT '應用名稱',
`host_name` varchar(32) NOT NULL COMMENT '主機名稱',
`collect_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '採集時間',
`version` varchar(32) NOT NULL DEFAULT '' COMMENT 'jvm版本',
`vendor` varchar(32) NOT NULL DEFAULT '' COMMENT '廠商',
`java_home` varchar(64) NOT NULL DEFAULT '' COMMENT '客戶端javahome路徑',
`loaded_class_count` int(11) NOT NULL DEFAULT '-1' COMMENT '已經載入的類數量',
`unloaded_class_count` int(11) NOT NULL DEFAULT '-1' COMMENT '已經解除安裝的類數量',
`total_loaded_class_count` int(11) NOT NULL DEFAULT '-1' COMMENT '累計載入的類數量',
`heap_init` float NOT NULL DEFAULT '-1' COMMENT '堆記憶體初始大小',
`heap_committed` bigint(20) NOT NULL DEFAULT '-1' COMMENT 'os分配給jvm的堆記憶體',
`heap_max` bigint(20) NOT NULL DEFAULT '-1' COMMENT '堆記憶體上限',
`heap_used` bigint(20) NOT NULL DEFAULT '-1' COMMENT '已經使用的堆記憶體大小',
`non_heap_init` bigint(20) NOT NULL DEFAULT '-1' COMMENT '非堆記憶體初始大小',
`non_heap_committed` bigint(20) NOT NULL DEFAULT '-1' COMMENT 'os分配給jvm的非堆記憶體',
`non_heap_max` bigint(20) NOT NULL DEFAULT '-1' COMMENT '非堆記憶體上限',
`non_heap_used` bigint(20) NOT NULL DEFAULT '-1' COMMENT '已經使用的非堆記憶體大小',
`current_thread_count` int(11) NOT NULL DEFAULT '-1' COMMENT '當前jvm執行緒總數',
`total_started_thread_count` int(11) NOT NULL DEFAULT '-1' COMMENT '累計啟動過的執行緒總數',
`peak_thread_count` int(11) NOT NULL DEFAULT '-1' COMMENT '執行緒數量最大值',
`daemon_thread_count` int(11) NOT NULL DEFAULT '-1' COMMENT 'daemon執行緒數量',
PRIMARY KEY (`id`),
KEY `app_name` (`app_name`,`host_name`,`collect_time`),
KEY `host_name` (`host_name`,`collect_time`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8mb4 COMMENT='jvm採集資訊表';
CREATE TABLE `t_jvm_gc_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`t_jvm_info_id` bigint(20) NOT NULL COMMENT 'jvm採集資訊表id',
`name` varchar(32) NOT NULL COMMENT 'gc型別名稱',
`gctime` bigint(20) NOT NULL DEFAULT '-1' COMMENT 'gc時間',
`gccount` bigint(20) NOT NULL DEFAULT '-1' COMMENT 'gc次數',
PRIMARY KEY (`id`),
KEY `t_jvm_info_id` (`t_jvm_info_id`),
CONSTRAINT `t_jvm_gc_info_ibfk_1` FOREIGN KEY (`t_jvm_info_id`) REFERENCES `t_jvm_info` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COMMENT='jvm gc採集資訊表';

建立可以自動生成資料的儲存過程

drop procedure if exists auto_fill ;
delimiter $$
create procedure auto_fill(pDb varchar(32),pTableList varchar(1024))
begin
declare done int default 0;
declare v_dbName varchar(128);
declare v_fullTableName varchar(128);
declare v_tableName varchar(128);
declare v_rowCount int;
declare cur_test CURSOR for select dbName,fullTableName,tableName,rowCount from tmp_table_info;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 臨時表,用於儲存拆分引數之後的結果.主要資訊就是資料庫名稱和表名稱
drop table if exists tmp_table_info;
create temporary table tmp_table_info
select pDb dbName,
concat(pDb,'.',substring_index ( value_str,',',1 )) fullTableName ,
substring_index ( value_str,',',1 ) tableName,
substring_index ( value_str,',',-1 ) rowCount
from (
select substring_index(substring_index(pTableList,';',b.id),';',-1) value_str
from
nums b
where b.id <= (length(pTableList) - length(replace(pTableList,';',''))+1)
) t1;
-- 禁用外來鍵
SET FOREIGN_KEY_CHECKS=0;
open cur_test;
repeat
fetch cur_test into v_dbName,v_fullTableName,v_tableName,v_rowCount;
if done!=1 then
set @sql=concat('insert ignore into ',v_dbName,'.',v_tableName,' select ');
select
@sql:=concat(@sql,
case
when extra='auto_increment' then concat('id,')
when data_type='int' then if(rowCount is null,'round(rand()*2147483647),',concat('round(rand()*',rowCount,'),'))
when data_type='bigint' then if(rowCount is null,'round(rand()*9223372036854775807),',concat('round(rand()*',rowCount,'),'))
when data_type='smallint' then 'round(rand()*32767),'
when data_type='tinyint' then 'round(rand()*127 ),'
when data_type='varchar' then concat('rand_string(',CHARACTER_MAXIMUM_LENGTH,',5),')
when data_type='date' then 'now()-interval round(90*rand()) day,'
when data_type='datetime' then 'now()-interval round(90*rand()) day,'
when data_type='timestamp' then 'now()-interval round(90*rand()) day,'
when data_type in('double','float') then 'round(rand()*32767,5),'
when data_type like '%text%' then concat('rand_string(2048,5),')
end
) s
from (
select
k.referenced_table_name,
k.referenced_column_name,
c.table_schema,
c.table_name,
c.column_name,
c.data_type,
c.CHARACTER_MAXIMUM_LENGTH,
c.extra,
t.rowCount
from information_schema.columns c
left join information_schema.KEY_COLUMN_USAGE k on(
c.table_schema=k.table_schema and
c.table_name=k.table_name and
c.column_name=k.column_name and
k.constraint_name
in (select constraint_name from information_schema.REFERENTIAL_CONSTRAINTS)
)
left join tmp_table_info t on(t.dbName=k.table_schema and t.tableName=k.table_name)
where (c.table_schema,c.table_name) =(v_dbName,v_tableName)
order by c.ORDINAL_POSITION
) t2
;
set @sql=left(@sql,char_length(@sql)-1);
select nullif ('please stand by...',@sql:=concat(@sql,' from nums where id<=',v_rowCount,';')) info;
prepare statement from @sql;
execute statement;
commit;
end if;
until done end repeat;
close cur_test;
-- 恢復外來鍵
SET FOREIGN_KEY_CHECKS=1;
end ;
$$
delimiter ;

執行儲存過程填充資料

call auto_fill('probe','t_jvm_gc_info,100000;t_jvm_info,2000');

過程第一個引數是資料庫名稱
第二個引數是表名和行數的字串列表.

測試資料生成自行刪除外來鍵約束即可

MySQL 用隨機資料填充外來鍵表

相關文章