MySQL用随机数据填充表

先创建数字辅助表

create table nums(id int not null primary key);

delimiter 
$$

create procedure pCreateNums(cnt int)
begin
    declare s int default 1;
    truncate table nums;
    while s<=cnt do
        insert into nums select s;
        set s=s+1;
    end while;
end 
$$

delimiter ;

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(100000);

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

        

          
        
        
        

          

          AI 代码解读
        
      
      

然后创建一个生成随机字符的函数

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 ;

        

          
        
        
        

          

          AI 代码解读
        
      
      

然后执行如下命令

set @table_schema='songod';
set @table_name='test';
set @row_count=10;
set @sql=concat('insert into ',@table_schema,'.',@table_name,' select ');
select 
nullif ('please stand by...',
    @sql:=concat(@sql,
        case 
                when data_type='int' then 'round(rand()*2147483647),'
                when data_type='bigint' then 'round(rand()*9223372036854775807),'
                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,'
        end
    )
) info
from information_schema.columns where table_schema=@table_schema and table_name=@table_name;
set @sql=left(@sql,char_length(@sql)-1);
select nullif ('please stand by...',@sql:=concat(@sql,' from nums where id<=',@row_count,';')) info;
prepare statement from @sql;
execute statement;
commit;

        

          
        
        
        

          

          AI 代码解读
        
      
      

其中

@table_schema是数据库名
@table_name是目标表名
@row_count是生成的行数

        

          
        
        
        

          

          AI 代码解读
        
      
      

这个不支持boolean类型,因为mysql在内部作为tinyint存储.
也有一些业务要求,比如State只有0,1两个取值.

可以在生成数据之后,再批量修改一下。
update test set c2=round(rand());

本文转自ICT时空 dbasdk博客，原文链接：MySQL用随机数据填充表 ，如需转载请自行联系原博主。