MySQL用随机数据填充表

浏览数：25 / 时间：2015年06月12日

先创建数字辅助表

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/

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

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

-- <for example> :

  -- 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 ;

然后执行如下命令

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;

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

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

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

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

MySQL用随机数据填充表