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语句拼成一条的效率会更高。

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u013399093/article/details/54585785

mysql 快速生成百万条测试数据

mysql 快速生成百万条测试数据 利用mysql内存表插入速度快的特点,先利用函数和存储过程在内存表中生成数据,然后再从内存表插入普通表中...
  • whzhaochao
  • whzhaochao
  • 2015-10-14 15:54:25
  • 12467

mysql快速生成百万条测试数据的方法

转自:mysql快速生成百万条测试数据的方法 — 没那么简单的博客 有时候我们需要对大数据进行测试,本地一般没有那么多数据,就需要我们自己生成一些。下面会借助内存表的特点进行生成百万条测试数据。创...
  • oahz4699092zhao
  • oahz4699092zhao
  • 2016-11-25 10:59:24
  • 6875

mysql随机数据生成并插入

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

Java(随机往mysql中导入一亿条数据)

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import...
  • qq_38046109
  • qq_38046109
  • 2017-05-04 17:03:15
  • 388

【MySQl】- 随机生成大量测试数据(测试用)

记录一个随机生成大量数据的存储过程(针对特定的表) 第一步: 创建表: CREATE TABLE `person` ( `id` bigint(20) unsigned NOT NULL, ...
  • LightOfMiracle
  • LightOfMiracle
  • 2017-06-30 16:18:48
  • 2496

mysql随机查询若干条数据

在mysql中查询5条不重复的数据,使用以下:SELECT * FROM `table` ORDER BY RAND() LIMIT 5就可以了。但是真正测试一下才发现这样效率非常低。一个15万余条的...
  • zxl315
  • zxl315
  • 2008-05-12 08:31:00
  • 15669

mysql产生随机数

mysql产生随机数      mysql产生随机数小结一下,可以为mysql的表生成大量的随机数:  1) 产生0到1000间的随机数     SELECT RAND() * 1...
  • bestlove12345
  • bestlove12345
  • 2017-01-17 14:48:30
  • 11363

MySQL取得某一范围随机数(MySQL随机数)

若要在i ≤ R ≤ j 这个范围得到一个随机整数R ,需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。 例如, 若要在7 到 12 的范围(包括7和12)内得到一...
  • sunflover454
  • sunflover454
  • 2015-12-30 17:45:11
  • 2447

mysql生成不重复随机数(unique number generation)

问题来源业务中有时会遇到要生成不重复随机数的情况,例如,新生成一个商品的id、房间的id、或者其他物品的id等。不愿意采用数据库中的记录id,同时又要保证新生成的id是不重复的。这就需要考验mysql...
  • dreamer2020
  • dreamer2020
  • 2016-07-27 21:50:35
  • 10791
收藏助手
不良信息举报
您举报文章:Mysql创建用户表并利用存储过程添加100万条随机用户数据
举报原因:
原因补充:

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