Mysql创建用户表并利用存储过程添加100万条随机用户数据

原创 2017年01月17日 18:58:49

1.创建用户表

CREATE TABLE user100w(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(10) NOT NULL,
  last_name VARCHAR(10) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
);

2.创建存储过程


DROP PROCEDURE IF EXISTS add_user;  
DELIMITER //
    create PROCEDURE add_user(in num INT)
    BEGIN
        DECLARE rowid INT DEFAULT 0;
        DECLARE firstname CHAR(1);
        DECLARE name1 CHAR(1);
        DECLARE name2 CHAR(1);
        DECLARE lastname VARCHAR(3) DEFAULT '';
        DECLARE sex CHAR(1);
        DECLARE score CHAR(2);
        WHILE rowid < num DO
        SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1); 
        SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
        SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
        SET sex=FLOOR(0 + (RAND() * 2));
        SET score= FLOOR(40 + (RAND() *60));
        SET rowid = rowid + 1;
        IF ROUND(RAND())=0 THEN 
        SET lastname =name1;
        END IF;
        IF ROUND(RAND())=1 THEN
        SET lastname = CONCAT(name1,name2);
        END IF;
        insert INTO user100w (first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid);  
        END WHILE;
    END //
DELIMITER ;

3.调用存储过程添加随机用户数据,比如随机插入1000000条数据:

call add_user(1000000);

执行结果如下:
这里写图片描述

从上述结果中可以看出,利用上面的存储过程添加100万条随机用户数据是非常耗时的,用了几乎一个小时的时间。下面是优化后的存储过程:

DROP PROCEDURE IF EXISTS add_user_optimizition;  
DELIMITER //
    create PROCEDURE add_user_optimizition(in num INT)
    BEGIN
        DECLARE rowid INT DEFAULT 0;
        DECLARE firstname CHAR(1);
        DECLARE name1 CHAR(1);
        DECLARE name2 CHAR(1);
        DECLARE lastname VARCHAR(3) DEFAULT '';
        DECLARE sex CHAR(1);
        DECLARE score CHAR(2);
        SET @exedata = "";
        WHILE rowid < num DO
            SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1); 
            SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
            SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
            SET sex=FLOOR(0 + (RAND() * 2));
            SET score= FLOOR(40 + (RAND() *60));
            SET rowid = rowid + 1;
            IF ROUND(RAND())=0 THEN 
            SET lastname =name1;
            END IF;
            IF ROUND(RAND())=1 THEN
            SET lastname = CONCAT(name1,name2);
            END IF;
            IF length(@exedata)>0 THEN
            SET @exedata = CONCAT(@exedata,',');
            END IF;
            SET @exedata=concat(@exedata,"('",firstname,"','",lastname,"','",sex,"','",score,"','",rowid,"')");
            IF rowid%1000=0
            THEN 
                SET @exesql =concat("insert into user100w_optimizition(first_name,last_name,sex,score,copy_id) values ", @exedata);
                prepare stmt from @exesql;
                execute stmt;
                DEALLOCATE prepare stmt;
                SET @exedata = "";
            END IF;
        END WHILE;
        IF length(@exedata)>0 
        THEN
            SET @exesql =concat("insert into user100w_optimizition(first_name,last_name,sex,score,copy_id) values ", @exedata);
            prepare stmt from @exesql;
            execute stmt;
            DEALLOCATE prepare stmt;
        END IF; 
    END //
DELIMITER ;

创建一个与上述用户表同样的表结构如下:

CREATE TABLE user100w_optimizition(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(10) NOT NULL,
  last_name VARCHAR(10) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
);

执行优化后的存储过程:

 call add_user_optimizition(1000001);

执行结果如下:
这里写图片描述

优化后的结果可以看到,百万条数据只需要50秒的执行时间,优化作用十分显著。对比优化前后的存储过程,不难发现我这里只是修改了一下sql的结构,把多条sql合并成一条执行。从这个案例中,我们总结出:如果一次性对同一个表插入多条数据,将insert语句拼成一条的效率会更高。

版权声明:本文为博主原创文章,未经博主允许不得转载。

mysql随机数据生成并插入

      dblp数据库中引用信息很少,平均一篇论文引用0.2篇。使用dblp做实验数据集的某篇论文提到,可以随机添加引用信息。受此启发,我打算为每一篇论文都添加20篇随机引用,于是就写出了如下的s...
  • kite1988
  • kite1988
  • 2010年04月10日 18:38
  • 4110

存储过程批量插入随机数据

前些天为了给系统做批量插入大数据随机量数据,此方法比较慢,效率低  本程序插入一万条记录用时大约在500s,效率太低 最后改用JDBC #创建存储过程 drop procedure if exi...
  • ZhiXianSen
  • ZhiXianSen
  • 2014年05月05日 11:39
  • 1256

一个100万数据(MYSQL)的网站,目前访问速度慢,如果让你优化,你会从哪些方面进行考虑,谈谈你的思路

1、应尽量避免在 where 子句中使用!=或   2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。   3、应尽量避免在 where 子...
  • m15712884682
  • m15712884682
  • 2016年11月05日 09:47
  • 837

关于批量插入数据之我见(100万级别的数据,mysql)

100万数据的插入操作
  • frinder
  • frinder
  • 2014年08月25日 23:57
  • 72458

Mysql批量插入随机数的方法

1 建立表格 CREATE TABLE `tables_a` ( `id` int(10) NOT NULL DEFAULT '0', `name` char(50) DEFAULT...
  • jintianhen1
  • jintianhen1
  • 2013年08月01日 09:28
  • 1757

mysql 某字段插入随机数!

UPDATE `表名` SET `字段名`=ceiling(rand()*500000+500000) WHERE (条件); 做个保存!
  • bobay
  • bobay
  • 2014年04月30日 15:57
  • 9729

使用mysql存储过程快速插入百万mysql测试数据

建表 用户表 CREATE TABLE `user` (          `id` BIGINT (20),          `name` VARCHAR(20)     )ENGI...
  • everdayPHP
  • everdayPHP
  • 2017年01月03日 17:04
  • 758

使用存储过程插入100000条测试数据

1:存储过程的sql如下: create procedure myproc() begin declare num int; set num=1; while num ...
  • xuanzhangran
  • xuanzhangran
  • 2017年05月18日 14:51
  • 1057

MySQL使用存储过程插入千万级数据如何提升效率?

-- MySQL测试任务:使用存储过程,往表中插入千万级数据,根据索引优化速度 -- 1.使用索引查询 -- 2.不使用索引查 -- 3.比较两者查询速度的差异 -- 1.创建索引测试表 DROP...
  • youcheng_ge
  • youcheng_ge
  • 2017年08月31日 01:35
  • 743

MySQL存储过程:批量为用户授权

编写出这些脚本的需求是把慢查日志写入数据库中,方便查看。 1. 由于默认的mysql.slow_log表使用的是csv数据引擎,不支持对数据进行索引,所以需要将其修改为MyISAM引擎,并对query...
  • yagas
  • yagas
  • 2014年09月19日 12:46
  • 2226
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Mysql创建用户表并利用存储过程添加100万条随机用户数据
举报原因:
原因补充:

(最多只允许输入30个字)