一步步教你利用Mysql儲存過程造百萬級資料

2023-03-19 06:03:48

1.準備工作

（1）由於是使用儲存過程，mysql從5.0版開始支援儲存過程，那麼需要mysql的版本在5.0或者以上。如何檢視mysql的版本，使用下面sql語句檢視：

（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函數及其功能如下：

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;

函數執行截圖：

（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;

函數執行截圖：

（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;

函數執行截圖：

（5）檢視資料庫中所有自定義函數資訊

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插入多少條資料

儲存過程執行截圖：

（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 的作用是設定使用者建立記憶體臨時表的大小，設定的值越大，能存進記憶體表的資料就越多。

儲存過程執行截圖：

（3）檢視儲存過程的狀態

-- 檢視資料庫所有的儲存過程
SHOW PROCEDURE STATUS;
-- 模糊查詢儲存過程
SHOW PROCEDURE STATUS LIKE 'add%';

模糊查詢結果：

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儲存過程造百萬級資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

一步步教你利用Mysql儲存過程造百萬級資料

目錄

1.準備工作

2.主要實現步驟

3.建立自動生成資料的函數

4.建立儲存過程

5.呼叫儲存過程

6.資料檢視驗證

總結

熱門文章