加入收藏 | 设为首页 | 会员中心 | 我要投稿 源码网 （https://www.900php.com/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

MySQL 用随机数据补充外键表

发布时间：2022-03-24 18:53:33 所属栏目：MySql教程 来源：互联网
导读：准备环境 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
       准备环境
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 '';
 
准备实验表.
    先创建一些带有外键约束的表.数据库名称是 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采集信息表';
 
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 ;
 
执行存储过程填充数据。

（编辑：源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
      站长推荐
      热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

        Copygight © 2008-2022 https://www.900php.com/ All Rights Reserved. 源码网