创建数据库和表

DROP database IF EXISTS `test_yc`;
create database `test_yc`;
use `test_yc`;
set global log_bin_trust_function_creators=1;
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT '1971-01-01 01:01:01',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建生成长度为n的随机字符串的函数

DELIMITER // -- '//'
DROP FUNCTION IF EXISTS `rand_strings` //
SET NAMES utf8 //
CREATE FUNCTION `rand_strings` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN 
    DECLARE char_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(char_str, FLOOR(1 + RAND()*62), 1));
        SET i = i+1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;

创建插入数据的存储过程

DELIMITER //
DROP PROCEDURE IF EXISTS `insert_test_user` //
CREATE PROCEDURE `insert_test_user`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE vote_num INT DEFAULT 0;
    DECLARE group_id INT DEFAULT 0;
    DECLARE status TINYINT DEFAULT 1;
    WHILE i < n DO
        SET status = FLOOR(1 + RAND()*2);
        INSERT INTO `test_user` VALUES (NULL, rand_strings(20),status, NOW());
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;  -- 改回默认的 MySQL delimiter：';'

调用存储过程生成10w数据

CALL insert_test_user(100000);

查询生成的数据

SELECT count(*) FROM `test_user`;