内射老阿姨1区2区3区4区_久久精品人人做人人爽电影蜜月_久久国产精品亚洲77777_99精品又大又爽又粗少妇毛片

MySQL用隨機(jī)數(shù)據(jù)填充外鍵表

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

準(zhǔn)備環(huán)境
1.創(chuàng)建數(shù)字輔助表
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);

數(shù)字輔助表的行數(shù)決定最后能生成的表行數(shù)的最大值.

2.創(chuàng)建生成隨機(jī)字符的函數(shù)

  1. DROP FUNCTION IF EXISTS rand_string;
  2. delimiter //
  3. CREATE FUNCTION rand_string(l_num int UNSIGNED,l_type tinyint UNSIGNED)
  4. RETURNS varchar(2000)
  5. BEGIN
  6.  -- Function : rand_string
  7.  -- Author : dbachina#dbachina.com
  8.  -- Date : 2010/5/30
  9.  -- l_num : The length of random string
  10.  -- l_type: The string type
  11.  -- 1.0-9
  12.  -- 2.a-z
  13.  -- 3.A-Z
  14.  -- 4.a-zA-Z
  15.  -- 5.0-9a-zA-Z
  16.  -- :
  17.   -- MySQL> select rand_string(12,5) random_string;
  18.   -- +---------------+
  19.   -- | random_string |
  20.   -- +---------------+
  21.   -- | 3KzGJCUJUplw |
  22.   -- +---------------+
  23.   -- 1 row in set (0.00 sec)
  24.  DECLARE i int UNSIGNED DEFAULT 0;
  25.  DECLARE v_chars varchar(64) DEFAULT '0123456789';
  26.   DECLARE result varchar (2000) DEFAULT '';
  27.  
  28.   IF l_type = 1 THEN
  29.     SET v_chars = '0123456789';
  30.   ELSEIF l_type = 2 THEN
  31.     SET v_chars = 'abcdefghijklmnopqrstuvwxyz';
  32.   ELSEIF l_type = 3 THEN
  33.     SET v_chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  34.   ELSEIF l_type = 4 THEN
  35.     SET v_chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  36.   ELSEIF l_type = 5 THEN
  37.     SET v_chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  38.   ELSE
  39.     SET v_chars = '0123456789';
  40.   END IF;
  41.  
  42.   WHILE i < l_num DO
  43.       SET result = concat( result,substr(v_chars,ceil(rand()*(length(v_chars)-1)),1) );
  44.     SET i = i + 1;
  45.   END WHILE;
  46.   RETURN result;
  47. END;
  48. //
  49. delimiter ;



準(zhǔn)備實(shí)驗(yàn)表.
    先創(chuàng)建一些帶有外鍵約束的表.數(shù)據(jù)庫(kù)名稱是 probe

  1. CREATE TABLE `t_jvm_info` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  3.   `app_name` varchar(32) NOT NULL COMMENT '應(yīng)用名稱',
  4.   `host_name` varchar(32) NOT NULL COMMENT '主機(jī)名稱',
  5.   `collect_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '采集時(shí)間',
  6.   `version` varchar(32) NOT NULL DEFAULT '' COMMENT 'jvm版本',
  7.   `vendor` varchar(32) NOT NULL DEFAULT '' COMMENT '廠商',
  8.   `java_home` varchar(64) NOT NULL DEFAULT '' COMMENT '客戶端javahome路徑',
  9.   `loaded_class_count` int(11) NOT NULL DEFAULT '-1' COMMENT '已經(jīng)加載的類數(shù)量',
  10.   `unloaded_class_count` int(11) NOT NULL DEFAULT '-1' COMMENT '已經(jīng)卸載的類數(shù)量',
  11.   `total_loaded_class_count` int(11) NOT NULL DEFAULT '-1' COMMENT '累計(jì)加載的類數(shù)量',
  12.   `heap_init` float NOT NULL DEFAULT '-1' COMMENT '堆內(nèi)存初始大小',
  13.   `heap_committed` bigint(20) NOT NULL DEFAULT '-1' COMMENT 'os分配給jvm的堆內(nèi)存',
  14.   `heap_max` bigint(20) NOT NULL DEFAULT '-1' COMMENT '堆內(nèi)存上限',
  15.   `heap_used` bigint(20) NOT NULL DEFAULT '-1' COMMENT '已經(jīng)使用的堆內(nèi)存大小',
  16.   `non_heap_init` bigint(20) NOT NULL DEFAULT '-1' COMMENT '非堆內(nèi)存初始大小',
  17.   `non_heap_committed` bigint(20) NOT NULL DEFAULT '-1' COMMENT 'os分配給jvm的非堆內(nèi)存',
  18.   `non_heap_max` bigint(20) NOT NULL DEFAULT '-1' COMMENT '非堆內(nèi)存上限',
  19.   `non_heap_used` bigint(20) NOT NULL DEFAULT '-1' COMMENT '已經(jīng)使用的非堆內(nèi)存大小',
  20.   `current_thread_count` int(11) NOT NULL DEFAULT '-1' COMMENT '當(dāng)前jvm線程總數(shù)',
  21.   `total_started_thread_count` int(11) NOT NULL DEFAULT '-1' COMMENT '累計(jì)啟動(dòng)過(guò)的線程總數(shù)',
  22.   `peak_thread_count` int(11) NOT NULL DEFAULT '-1' COMMENT '線程數(shù)量最大值',
  23.   `daemon_thread_count` int(11) NOT NULL DEFAULT '-1' COMMENT 'daemon線程數(shù)量',
  24.   PRIMARY KEY (`id`),
  25.   KEY `app_name` (`app_name`,`host_name`,`collect_time`),
  26.   KEY `host_name` (`host_name`,`collect_time`)
  27. ) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8mb4 COMMENT='jvm采集信息表';

  29. CREATE TABLE `t_jvm_gc_info` (
  30.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  31.   `t_jvm_info_id` bigint(20) NOT NULL COMMENT 'jvm采集信息表id',
  32.   `name` varchar(32) NOT NULL COMMENT 'gc類型名稱',
  33.   `gctime` bigint(20) NOT NULL DEFAULT '-1' COMMENT 'gc時(shí)間',
  34.   `gccount` bigint(20) NOT NULL DEFAULT '-1' COMMENT 'gc次數(shù)',
  35.   PRIMARY KEY (`id`),
  36.   KEY `t_jvm_info_id` (`t_jvm_info_id`),
  37.   CONSTRAINT `t_jvm_gc_info_ibfk_1` FOREIGN KEY (`t_jvm_info_id`) REFERENCES `t_jvm_info` (`id`)
  38. ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COMMENT='jvm gc采集信息表';

創(chuàng)建可以自動(dòng)生成數(shù)據(jù)的存儲(chǔ)過(guò)程


  1. drop procedure if exists auto_fill ;
  2. delimiter $$
  3. create procedure auto_fill(pDb varchar(32),pTableList varchar(1024))
  4. begin
  5.     declare done int default 0;
  6.     declare v_dbName varchar(128);
  7.     declare v_fullTableName varchar(128);
  8.     declare v_tableName varchar(128);
  9.     declare v_rowCount int;
  10.     declare cur_test CURSOR for select dbName,fullTableName,tableName,rowCount from tmp_table_info;
  11.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  13.     -- 臨時(shí)表,用于保存拆分參數(shù)之后的結(jié)果.主要信息就是數(shù)據(jù)庫(kù)名稱和表名稱
  14.     drop table if exists tmp_table_info;
  15.     create temporary table tmp_table_info
  16.     select    pDb dbName,
  17.      concat(pDb,'.',substring_index ( value_str,',',1 )) fullTableName ,
  18.      substring_index ( value_str,',',1 ) tableName,
  19.      substring_index ( value_str,',',-1 ) rowCount
  20.     from (
  21.         select substring_index(substring_index(pTableList,';',b.id),';',-1) value_str
  22.         from
  23.         nums b
  24.         where b.id <= (length(pTableList) - length(replace(pTableList,';',''))+1)
  25.     ) t1;
  26.     
  27.     -- 禁用外鍵
  28.     SET FOREIGN_KEY_CHECKS=0;
  29.     open cur_test;
  30.     repeat
  31.         fetch cur_test into v_dbName,v_fullTableName,v_tableName,v_rowCount;
  32.         if done!=1 then

  34.             set @sql=concat('insert ignore into ',v_dbName,'.',v_tableName,' select ');
  35.             select
  36.             @sql:=concat(@sql,
  37.                 case
  38.                     when extra='auto_increment' then concat('id,')
  39.                     when data_type='int' then if(rowCount is null,'round(rand()*2147483647),',concat('round(rand()*',rowCount,'),'))
  40.                     when data_type='bigint' then if(rowCount is null,'round(rand()*9223372036854775807),',concat('round(rand()*',rowCount,'),'))
  41.                     when data_type='smallint' then 'round(rand()*32767),'
  42.                     when data_type='tinyint' then 'round(rand()*127 ),'
  43.                     when data_type='varchar' then concat('rand_string(',CHARACTER_MAXIMUM_LENGTH,',5),')
  44.                     when data_type='date' then 'now()-interval round(90*rand()) day,'
  45.                     when data_type='datetime' then 'now()-interval round(90*rand()) day,'
  46.                     when data_type='timestamp' then 'now()-interval round(90*rand()) day,'
  47.                     when data_type in('double','float') then 'round(rand()*32767,5),'
  48.                     when data_type like '%text%' then concat('rand_string(2048,5),')
  49.                 end
  50.             ) s
  51.             from (
  52.                 select
  53.                     k.referenced_table_name,
  54.                     k.referenced_column_name,
  55.                     c.table_schema,
  56.                     c.table_name,
  57.                     c.column_name,
  58.                     c.data_type,
  59.                     c.CHARACTER_MAXIMUM_LENGTH,
  60.                     c.extra,
  61.                     t.rowCount
  62.                 from information_schema.columns c
  63.                 left join information_schema.KEY_COLUMN_USAGE k on(
  64.                     c.table_schema=k.table_schema and
  65.                     c.table_name=k.table_name and
  66.                     c.column_name=k.column_name and
  67.                     k.constraint_name
  68.                         in    (select constraint_name from information_schema.REFERENTIAL_CONSTRAINTS)
  69.                 )
  70.                 left join tmp_table_info t on(t.dbName=k.table_schema and t.tableName=k.table_name)
  71.                 where (c.table_schema,c.table_name) =(v_dbName,v_tableName)
  72.                 order by c.ORDINAL_POSITION
  73.             ) t2
  74.             ;
  75.             set @sql=left(@sql,char_length(@sql)-1);
  76.             select nullif ('please stand by...',@sql:=concat(@sql,' from nums where id<=',v_rowCount,';')) info;
  77.             prepare statement from @sql;
  78.             execute statement;
  79.             commit;
  80.         end if;
  81.     until done end repeat;
  82.     close cur_test;
  83.         
  84.     -- 恢復(fù)外鍵
  85.     SET FOREIGN_KEY_CHECKS=1;


  88. end ;
  89. $$
  90. delimiter ;

執(zhí)行存儲(chǔ)過(guò)程填充數(shù)據(jù)

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

過(guò)程第一個(gè)參數(shù)是 數(shù)據(jù)庫(kù)名稱
第二個(gè)參數(shù)是 表名和行數(shù)的字符串列表.

測(cè)試數(shù)據(jù)生成自行刪除外鍵約束即可



文章標(biāo)題：MySQL用隨機(jī)數(shù)據(jù)填充外鍵表
URL鏈接：http://www.rwnh.cn/article0/jsdooo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、軟件開發(fā)、手機(jī)網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站導(dǎo)航、品牌網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

小程序開發(fā)
沙河市| 青浦区| 贵港市| 凭祥市| 新乡市| 德阳市| 榆中县| 衡水市| 灵石县| 成都市| 阳春市| 盐池县| 梓潼县| 岳阳市| 金坛市| 鹤山市| 高平市| 萨迦县| 龙游县| 连云港市| 大名县| 堆龙德庆县| 嘉鱼县| 青阳县| 巴楚县| 枝江市| 枣庄市| 灵川县| 栖霞市| 方城县| 白玉县| 西盟| 郯城县| 高邑县| 山西省| 枣阳市| 北安市| 谷城县| 会同县| 铁岭市| 瑞金市|