搜尋

首頁 >資料庫 >mysql教程 >怎麼用Mysql預存程序造百萬級數據

怎麼用Mysql預存程序造百萬級數據

WBOY
WBOY轉載
2023-06-03 19:40:011197瀏覽

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, &#39;0123456789&#39; )));
    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 &#39;abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789&#39;;
    DECLARE
        return_str VARCHAR ( 30 ) DEFAULT &#39;&#39;;
    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 &#39;add%&#39;;

模糊查詢結果：

怎麼用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預存程序造百萬級數據的詳細內容。更多資訊請關注PHP中文網其他相關文章！

sql mysql String 字符串 循环 Length position 数据库
陳述：
本文轉載於：yisu.com。如有侵權，請聯絡admin@php.cn刪除
上一篇：如何用PHP從MySQL資料庫新增、更新和刪除數據下一篇：如何用PHP從MySQL資料庫新增、更新和刪除數據

相關文章

看更多