参考:
Http://blog.itpub
.net/29254281/viewspace-1686302/
准备环境
1.创建数字辅助表
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);
数字辅助表的行数决定最后能生成的表行数的最大值.
2.创建生成随机字符的函数
-
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 ;
0