How to use Mysql stored procedures to create millions of data

1. Preparation

(1) Since stored procedures are used, mysql supports stored procedures starting from version 5.0, so the version of mysql needs to be 5.0 or above. How to check the version of mysql, use the following sql statement to check:

(2) Create two tables with the same table structure but different storage engines, as shown below , Ordinary tables use the default INNODB storage engine after mysql5.5 version, and memory tables use the MEMORY storage engine.

Since MEMORY storage is not commonly used, here is a brief introduction to its characteristics: the MEMORY engine table structure is created on the disk, all data is placed in the memory, and the access speed is fast, but when MySQL is restarted or once the system crashes , the data will disappear, but the structure will still exist.

# 创建普通表
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. Main implementation steps

(1) Create a function that automatically generates data and use it when inserting;

(2) Create a storage procedure for inserting data into the memory table and call The created data generation function;

(3) Create memory table data and insert it into the ordinary table stored procedure;

(4) Call the stored procedure.

(5) Data viewing and verification

3. Create a function that automatically generates data

(1) Generate n random numbers

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;

Function running screenshot :

The mysql functions used in the script and their functions are as follows:

a.concat(): Concatenate multiple strings into one string.

b.Floor(): Round down.

c.substring(string, position, length)

The first parameter: string refers to the original string that needs to be intercepted.

The second parameter: position refers to the position from which to intercept the substring. The position encoding sequence number of the character here starts from 1. If position is a negative number, the position is counted from right to left.

The third parameter: length refers to the length of the string that needs to be intercepted. If not written, all characters from the beginning of position to the last character will be intercepted by default.

d.RAND(): can only generate random decimals between 0 and 1.

(2) Create a function to randomly generate a mobile phone number

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;

Function running screenshot:

(3) Create a function to randomly generate a username

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;

Function running screenshot:

# (4) Randomly generate user status function

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;

Function running screenshot:

(5) View all custom function information in the database

4. Create a stored procedure

(1) Create a stored procedure for inserting data into the memory table

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;

The input parameter n indicates how many pieces of data are inserted into the memory table memory_user_info

Screenshot of the stored procedure running:

(2) Create memory table data and insert ordinary table stored procedure

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;

This is the main stored procedure and the entrance. It uses cyclic insertion and deletion of the memory table to generate data in batches. There is no need to change the default max_heap_table_size value of mysql (the default value is 16M). The function of max_heap_table_size is to configure the size of the temporary memory table created by the user. The larger the configured value, the more data can be stored in the memory table.

Stored procedure running screenshot:

(3) View the status of the stored procedure

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

Fuzzy query results:

5. Call stored procedures

mysql calls the execution of stored procedures a call, so the statement used by mysql to execute a stored procedure is CALL. CALL accepts the name of the stored procedure and any parameters that need to be passed to it.

By calling the add_user_info stored procedure, continuously insert the memory table memory_user_info in a loop, then obtain the data from the memory table and insert it into the ordinary table user_info, and then delete the memory table data, and this cycle continues until the end of the cycle. Loop 100 times, generating 10,000 pieces of data each time, and a total of one million pieces of data.

CALL add_user_info(100,10000);

6. Data viewing and verification

When the ordinary table data reaches 60,000, it takes about 23 minutes. Based on this time, it is estimated that it will take 6 hours to generate 1 million data about. The time-consuming point is mainly in the four functions that randomly generate field data. If the field data does not require randomness, it will be much faster.

The data is recorded as follows:

The above is the detailed content of How to use Mysql stored procedures to create millions of data. For more information, please follow other related articles on the PHP Chinese website!