mysql制作千万的测试数据

最新推荐文章于 2024-01-08 15:46:13 发布

大宇进阶之路

最新推荐文章于 2024-01-08 15:46:13 发布

阅读量332

点赞数

CC 4.0 BY-SA版权

原文链接：https://www.cnblogs.com/qmfsun/p/4881919.html

本文详细介绍了如何使用SQL语句创建数据库表结构，并通过存储过程实现批量数据插入，涵盖了表字段定义、数据类型设置、存储引擎选择及存储过程的循环插入机制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DROP TABLE IF EXISTS usertb;
CREATE TABLE usertb(   
id serial,    
uname  varchar(20) ,   
ucreatetime  datetime  ,   
age  int(11))   
ENGINE=MYISAM 
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci   
AUTO_INCREMENT=1   
ROW_FORMAT=COMPACT;    

delimiter $$ 
SET AUTOCOMMIT = 0$$   
 
 DROP PROCEDURE IF EXISTS test1;
create  procedure test1()  
begin
declare v_cnt decimal (10)  default 0 ; 
dd:loop            
        insert  into usertb values         
        (null,'用户1','2010-01-01 00:00:00',20),         
        (null,'用户2','2010-01-01 00:00:00',20),         
        (null,'用户3','2010-01-01 00:00:00',20),         
        (null,'用户4','2010-01-01 00:00:00',20),         
        (null,'用户5','2011-01-01 00:00:00',20),         
        (null,'用户6','2011-01-01 00:00:00',20),         
        (null,'用户7','2011-01-01 00:00:00',20),         
        (null,'用户8','2012-01-01 00:00:00',20),         
        (null,'用户9','2012-01-01 00:00:00',20),         
        (null,'用户0','2012-01-01 00:00:00',20)             
                ;                   
        commit;                     
        set v_cnt = v_cnt+10 ;                            
            if  v_cnt = 100 then leave dd;       #10000000                    
            end if;          
        end loop dd ; 
end;$$   
 
delimiter ;    

call test1;

DROP TABLE IF EXISTS `vote_record_memory`;
CREATE TABLE `vote_record_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_time` 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;

DROP TABLE IF EXISTS `vote_record`;
CREATE TABLE `vote_record` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除',
    `create_time` datetime  NOT NULL DEFAULT '2001-00-00 00:00:00' COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH COMMENT '用户ID哈希索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票记录表';

DELIMITER //
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8 //
CREATE FUNCTION `rand_string` (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 //

DROP PROCEDURE IF EXISTS `add_vote_record_memory` //
CREATE PROCEDURE `add_vote_record_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_record_memory` VALUES (NULL, rand_string(20), vote_num, group_id, status, NOW());
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ; 

CALL add_vote_record_memory(10);
SELECT count(*) FROM `vote_record_memory`;

INSERT INTO vote_record SELECT * FROM `vote_record_memory`;

SELECT count(*) FROM `vote_record`;