参考:
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用随机数据填充外键表
文章链接：
http://6mz.cn/article/jsdooo.html