MYSQL用存储过程创建百万级测试数据表

1.创建随机字符串函数，便于创建名称

-- delimiter $$
-- 执行出现“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled
-- (you *might* want to use the less safe log_bin_trust_function_creators variable)” 错误时需要先设置log_bin_trust_function_creators
-- set global log_bin_trust_function_creators=1;

DROP function if EXISTS rand_string;
#创建一个指定字符个数的函数
create function rand_string(n INT)
#返回字符串，注意：此处关键字是returns 而不是return
returns varchar(255)
BEGIN
    #定义一个临时变量，给变量赋值'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    # 定义返回结果字符串
    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()*52),1));
        set i=i+1;
    END while;
    RETURN return_str;
END

2.创建随机编号生成函数