首页 > 资讯 > 数据库 >一步步教你利用Mysql存储过程造百万级数据

891

分享到

一步步教你利用Mysql存储过程造百万级数据

mysql存储过程怎么写 mysql 存储过程实例 MySQL创建存储过程 2023-03-19 16:03:22 891人浏览八月长安

摘要

目录1.准备工作2.主要实现步骤3.创建自动生成数据的函数4.创建存储过程5.调用存储过程6.数据查看验证总结1.准备工作（1）由于是使用存储过程，Mysql从5.0版开始支持存储过程，那么需要mysql的版本在5.0

1.准备工作

（1）由于是使用存储过程，Mysql从5.0版开始支持存储过程，那么需要mysql的版本在5.0或者以上。如何查看mysql的版本，使用下面sql语句查看：

一步步教你利用Mysql存储过程造百万级数据

（2）创建两张表，表结构一致，但使用的存储引擎不一样，如下所示，普通表使用mysql5.5版本后默认的INNODB存储引擎，内存表使用MEMORY存储引擎。

由于MEMORY存储不常用这里简单说一下其特点：MEMORY引擎表结构创建在磁盘上，数据全部放在内存中，访问速度较快，但是当MySQL重启后或者一旦系统奔溃的话，数据都会消失，结构还存在。

# 创建普通表
CREATE TABLE `user_info` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `name` VARCHAR ( 30 ) NOT NULL COMMENT '用户名',
    `phone` VARCHAR ( 11 ) NOT NULL COMMENT '手机号',
    `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用户状态:停用0，启动1',
    `create_time` datetime NOT NULL COMMENT '创建时间',
    PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 10001 CHARACTER 
SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表';
 
# 创建内存表
CREATE TABLE `memory_user_info` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `name` VARCHAR ( 30 ) NOT NULL COMMENT '用户名',
    `phone` VARCHAR ( 11 ) NOT NULL COMMENT '手机号',
    `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用户状态:停用0，启动1',
    `create_time` datetime NOT NULL COMMENT '创建时间',
    PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = MEMORY AUTO_INCREMENT = 10001 CHARACTER 
SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息内存表';

2.主要实现步骤

（1）创建自动生成数据的函数，插入时使用；

（2）创建插入内存表数据存储过程，调用已创建好的数据生成函数；

（3）创建内存表数据插入普通表存储过程；

（4）调用存储过程。

（5）数据查看验证

3.创建自动生成数据的函数

（1）生成n个随机数字

DELIMITER //
DROP FUNCTioN
IF
    EXISTS randomNum // CREATE FUNCTION randomNum (
        n INT,
        chars_str VARCHAR ( 10 )) RETURNS VARCHAR ( 255 ) BEGIN
    DECLARE
        return_str VARCHAR ( 255 ) DEFAULT '';
    DECLARE
        i INT DEFAULT 0;
    WHILE
            i < n DO
            
            SET return_str = concat(
                return_str,
            substring( chars_str, FLOOR( 1 + RAND()* 10 ), 1 ));
        
        SET i = i + 1;
        
    END WHILE;
    RETURN return_str;
    
END // 
DELIMITER;

函数运行截图：

一步步教你利用Mysql存储过程造百万级数据

脚本所用到的mysql函数及其功能如下：

a.concat()：将多个字符串连接成一个字符串。

b.Floor()：向下取整。

c.substring(string, position, length)

第一个参数：string指的是需要截取的原字符串。

第二个参数：position指的是从哪个位置开始截取子字符串，这里字符的位置编码序号是从1开始，若position为负数则从右往左开始数位置。

第三个参数：length指的是需要截取的字符串长度，如果不写，则默认截取从position开始到最后一位的所有字符。

d.RAND()：只能生成0到1之间的随机小数。

（2）创建随机生成手机号函数

DELIMITER //
DROP FUNCTION
IF
    EXISTS getPhone // CREATE FUNCTION getPhone () RETURNS VARCHAR ( 11 ) BEGIN
    DECLARE
        head CHAR ( 3 );
    DECLARE
        phone VARCHAR ( 11 );
    DECLARE
        bodys VARCHAR ( 65 ) DEFAULT "130 131 132 133 134 135 136 137 138 139 186 187 189 151 157";
    DECLARE
        STARTS INT;
    
    SET STARTS = 1+floor ( rand()* 15 )* 4;
    
    SET head = trim(
    substring( bodys, STARTS, 3 ));
    
    SET phone = trim(
        concat(
            head,
        randomNum ( 8, '0123456789' )));
    RETURN phone;
    
END // 
DELIMITER;

函数运行截图：

一步步教你利用Mysql存储过程造百万级数据

（3)创建随机生成用户名函数

DELIMITER //
DROP FUNCTION
IF
    EXISTS randName // CREATE FUNCTION randName ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 DETERMINISTIC BEGIN
    DECLARE
        chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE
        return_str VARCHAR ( 30 ) DEFAULT '';
    DECLARE
        i INT DEFAULT 0;
    WHILE
            i < n DO
            
            SET return_str = concat(
                return_str,
            substring( chars_str, FLOOR( 1 + RAND() * 62 ), 1 ));
        
        SET i = i + 1;
        
    END WHILE;
    RETURN return_str;
 
END // 
DELIMITER;

函数运行截图：

一步步教你利用Mysql存储过程造百万级数据

（4）随机生成用户状态函数

DELIMITER //
DROP FUNCTION
IF
    EXISTS randStatus // CREATE FUNCTION randStatus ( ) RETURNS TINYINT ( 1 ) BEGIN
    DECLARE
        user_status INT ( 1 ) DEFAULT 0;
    
    SET user_status =
    IF
        ( FLOOR( RAND() * 10 ) <= 4, 1, 0 );
    RETURN user_status;
 
END // 
DELIMITER;

函数运行截图：

一步步教你利用Mysql存储过程造百万级数据

（5）查看数据库中所有自定义函数信息

一步步教你利用Mysql存储过程造百万级数据

4.创建存储过程

（1）创建插入内存表数据存储过程

DELIMITER //
DROP FUNCTION
IF
    EXISTS randStatus // CREATE FUNCTION randStatus ( ) RETURNS TINYINT ( 1 ) BEGIN
    DECLARE
        user_status INT ( 1 ) DEFAULT 0;
    
    SET user_status =
    IF
        ( FLOOR( RAND() * 10 ) <= 4, 1, 0 );
    RETURN user_status;
 
END // 
DELIMITER;

入参n是多少就表示往内存表memory_user_info插入多少条数据

存储过程运行截图：

一步步教你利用Mysql存储过程造百万级数据

（2）创建内存表数据插入普通表存储过程

DELIMITER //
DROP PROCEDURE
IF
    EXISTS add_user_info // CREATE PROCEDURE `add_user_info` ( IN n INT, IN count INT ) BEGIN
    DECLARE
        i INT DEFAULT 1;
    WHILE
            ( i <= n ) DO
            CALL add_memory_user_info ( count );
        INSERT INTO user_info SELECT
        * 
        FROM
            memory_user_info;
        DELETE 
        FROM
            memory_user_info;
        
        SET i = i + 1;
        
    END WHILE;
 
END // 
DELIMITER;

这是最主要的存储过程，也是入口，利用对内存表的循环插入和删除来实现批量生成数据，不需要更改mysql默认的max_heap_table_size值（默认值是16M），max_heap_table_size 的作用是配置用户创建内存临时表的大小，配置的值越大，能存进内存表的数据就越多。

存储过程运行截图：

一步步教你利用Mysql存储过程造百万级数据

（3）查看存储过程的状态

-- 查看数据库所有的存储过程
SHOW PROCEDURE STATUS;
-- 模糊查询存储过程
SHOW PROCEDURE STATUS LIKE 'add%';

模糊查询结果：

一步步教你利用Mysql存储过程造百万级数据

5.调用存储过程

mysql称存储过程的执行为调用，因此mysql执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。

通过调用add_user_info存储过程，不断循环插入内存表memory_user_info，再从内存表获取数据插入普通表user_info，然后删除内存表数据，以此循环直至循环结束。循环100次，每次生成10000条数据，共生成一百万条数据。

CALL add_user_info(100,10000);

6.数据查看验证

在普通表数据达到6万条时，已经耗时大概在23分钟左右，以这个时间推算，100万数据生成预计需要6小时左右。耗时的点主要是在四个随机生成字段数据的函数上。如果字段数据不要求随机，那么将会快很多。

一步步教你利用Mysql存储过程造百万级数据

数据记录如下效果：

一步步教你利用Mysql存储过程造百万级数据

总结

到此这篇关于一步步教你利用Mysql存储过程造百万级数据的文章就介绍到这了,更多相关Mysql存储过程造百万级数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: 一步步教你利用Mysql存储过程造百万级数据

本文链接: https://www.lsjlt.com/news/200023.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

一步步教你利用Mysql存储过程造百万级数据

目录

1.准备工作

2.主要实现步骤

3.创建自动生成数据的函数

4.创建存储过程

5.调用存储过程

6.数据查看验证

总结

本篇文章演示代码以及资料文档资料下载

一步步教你利用Mysql存储过程造百万级数据

怎么用Mysql存储过程造百万级数据

ASP 大数据 API 教程：一步步教你打造数据驱动的应用！

Mysql使用存储过程快速添加百万数据的示例代码

MySQL如何利用存储过程快速生成100万条数据详解

Go 同步存储教程：让你的数据随时随地可用

redis比mysql快多少

redis锁如何实现

redis是非关系型数据库吗

redis缓存穿透如何解决

redis的原理是什么

redis如何查看数据

redis的缓存击穿和缓存穿透一样吗

如何解决redis缓存击穿

redis读写锁如何解决

redis删除缓存时失败怎么办