MySQL用隨機資料填充表

dbasdk發表於2015-06-24
MySql隨機
先建立數字輔助表
  1. create table nums(id int not null primary key);

  3. delimiter $$
  4. create procedure pCreateNums(cnt int)
  5. begin
  6.     declare s int default 1;
  7.     truncate table nums;
  8.     while s<=cnt do
  9.         insert into nums select s;
  10.         set s=s+1;
  11.     end while;
  12. end $$
  13. delimiter ;

  15. delimiter $$
  16. create procedure pFastCreateNums(cnt int)
  17. begin
  18.     declare s int default 1;
  19.     truncate table nums;
  20.     insert into nums select s;
  21.     while s*2<=cnt do
  22.         insert into nums select id+s from nums;
  23.         set s=s*2;
  24.     end while;
  25. end $$
  26. delimiter ;
call pFastCreateNums(100000);

http://blog.itpub.net/29254281/viewspace-1362897/

然後建立一個生成隨機字元的函式
  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 ; 
然後執行如下命令

  1. set @table_schema='songod';
  2. set @table_name='test';
  3. set @row_count=10;
  4. set @sql=concat('insert into ',@table_schema,'.',@table_name,' select ');
  5. select
  6. nullif ('please stand by...',
  7.     @sql:=concat(@sql,
  8.         case
  9.                 when data_type='int' then 'round(rand()*2147483647),'
  10.                 when data_type='bigint' then 'round(rand()*9223372036854775807),'
  11.                 when data_type='smallint' then 'round(rand()*32767),'
  12.                 when data_type='tinyint' then 'round(rand()*127 ),'
  13.                 when data_type='varchar' then concat('rand_string(',CHARACTER_MAXIMUM_LENGTH,',5),')
  14.                 when data_type='date' then 'now()-interval round(90*rand()) day,'
  15.                 when data_type='datetime' then 'now()-interval round(90*rand()) day,'
  16.                 when data_type='timestamp' then 'now()-interval round(90*rand()) day,'
  17.         end
  18.     )
  19. ) info
  20. from information_schema.columns where table_schema=@table_schema and table_name=@table_name;
  21. set @sql=left(@sql,char_length(@sql)-1);
  22. select nullif ('please stand by...',@sql:=concat(@sql,' from nums where id<=',@row_count,';')) info;
  23. prepare statement from @sql;
  24. execute statement;
  25. commit;
其中
@table_schema是資料庫名
@table_name是目標表名
@row_count是生成的行數

這個不支援boolean型別,因為mysql在內部作為tinyint儲存.
也有一些業務要求,比如State只有0,1兩個取值.

可以在生成資料之後,再批次修改一下。
update test set c2=round(rand());

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-1710655/，如需轉載，請註明出處，否則將追究法律責任。

相關文章