mysql 生成数据

创建表

set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

CREATE TABLE `vote_records_memory` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL DEFAULT '',
`vote_num` int(10) unsigned NOT NULL DEFAULT '0',
`group_id` int(10) unsigned NOT NULL DEFAULT '0',
`status` tinyint(2) unsigned NOT NULL DEFAULT '1',
`create_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

生成函数

set global log_bin_trust_function_creators=1;
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 $$
CREATE PROCEDURE `insert_vote_records_memory`(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 vote_num = FLOOR(1 + RAND() * 10000);
SET group_id = FLOOR(0 + RAND()*3);
SET status = FLOOR(1 + RAND()*2);
INSERT INTO `vote_records_memory` VALUES (NULL, rand_strings(20), vote_num, group_id, status, NOW());
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;