1.準備工作
(1)由於是使用預存程序,mysql從5.0版開始支援預存程序,那麼需要mysql的版本在5.0或以上。如何查看mysql的版本,使用下面sql語句查看:

(2)建立兩張表,表結構一致,但使用的儲存引擎不一樣,如下所示,普通表使用mysql5.5版本後預設的INNODB儲存引擎,記憶體表使用MEMORY儲存引擎。
由於MEMORY儲存不常用這裡簡單說一下其特點:MEMORY引擎表結構創建在磁碟上,資料全部放在記憶體中,存取速度較快,但是當MySQL重啟後或一旦系統奔潰的話,資料都會消失,結構還存在。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 创建普通表
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個隨機數字
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DELIMITER
DROP FUNCTION
IF
EXISTS 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)建立隨機產生手機號碼函數
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | DELIMITER
DROP FUNCTION
IF
EXISTS getPhone
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)建立隨機產生使用者名稱函數
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DELIMITER
DROP FUNCTION
IF
EXISTS randName
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)隨機產生使用者狀態函數
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DELIMITER
DROP FUNCTION
IF
EXISTS randStatus
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)建立插入記憶體表資料儲存程序
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DELIMITER
DROP FUNCTION
IF
EXISTS randStatus
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)創建內存表數據插入普通表存儲過程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | DELIMITER
DROP PROCEDURE
IF
EXISTS add_user_info
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)檢視預存程序的狀態
1 2 3 4 | -- 查看数据库所有的存储过程
SHOW PROCEDURE STATUS;
-- 模糊查询存储过程
SHOW PROCEDURE STATUS LIKE 'add%';
|
登入後複製
模糊查詢結果:

5.呼叫預存程序
mysql稱預存程序的執行為調用,因此mysql執行預存程序的語句為CALL。 CALL接受預存程序的名字以及需要傳遞給它的任意參數。
透過呼叫add_user_info存儲過程,不斷循環插入內存表memory_user_info,再從內存表獲取數據插入普通表user_info,然後刪除內存表數據,以此循環直至循環結束。循環100次,每次產生10000條數據,共產生一百萬個數據。
1 | CALL add_user_info(100,10000);
|
登入後複製
6.資料檢視驗證
在一般表資料達到6萬個時,已經耗時大概在23分鐘左右,以這個時間推算,100萬資料產生預計需要6小時左右。耗時的點主要是在四個隨機產生欄位資料的函數上。如果欄位資料不要求隨機,那麼將會快很多。

資料記錄如下效果:

以上是怎麼用Mysql預存程序造百萬級數據的詳細內容。更多資訊請關注PHP中文網其他相關文章!