當前位置：首頁 > 數據庫 > Mysql > 詳細頁面

一步步教你利用Mysql存儲過程造百萬級數據

時間：2023-03-19來源：系統城裝機大師作者：佚名

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 // 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）創建隨機生成手機號函數

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 // 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)創建隨機生成用戶名函數

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 // 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）隨機生成用戶狀態函數

1
2
3
4
5
6
7
8
9
10
11
12
13
14 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）創建插入內存表數據存儲過程

入參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 // 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;