mysql 随机数生成_MYSQL产生随机数

生成随机字符串的函数：

set global log_bin_trust_function_creators=TRUE;

CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1

BEGIN

DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

DECLARE return_str varchar(255) DEFAULT '' ;

DECLARE i INT DEFAULT 0;

WHILE i < n DO

SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));

SET i = i +1;

END WHILE;

RETURN return_str;

END

根据表结构生成随机的数据的存储过程：

CREATE PROCEDURE `add_log`(IN n int)

BEGIN

DECLARE i INT DEFAULT 1;

WHILE (i <= n ) DO

INSERT into tb_fws_nat_log (date, time, src, spt, dst, dpt, proto, conv_ip, conv_port, result)

VALUEs (concat('201',(floor(rand()*6)+1),'-',floor(rand()*12)+1,'-',floor(rand()*31),' ',floor(10+rand()*10),':',floor(10+rand()*49),':',floor(10+rand()*49)),

FLOOR(RAND() * 99999999),FLOOR(RAND() * 4294967295),FLOOR(RAND() * 65535),FLOOR(RAND() * 4294967295),FLOOR(RAND() * 65535),

rand_string(12),FLOOR(RAND() * 4294967295),FLOOR(RAND() * 65535),rand_string(8));

set i=i+1;

END WHILE;

END

调用存储过程插入1000条随机数据：

CALL add_log(1000);