수백만 개의 데이터를 생성하기 위해 MySQL 저장 프로시저를 사용하는 방법

1. 준비

(1) 저장 프로시저를 사용하므로 mysql은 버전 5.0부터 저장 프로시저를 지원하므로 mysql 버전은 5.0 이상이어야 합니다. mysql의 버전을 확인하는 방법은 다음 sql 문을 사용하여 확인한다.

(2) 동일한 테이블 구조로 두 개의 테이블을 생성하지만 서로 다른 스토리지 엔진을 사용한다. 아래와 같이 일반 테이블은 mysql5.5를 사용한다. INNODB 스토리지 엔진과 메모리 테이블은 기본적으로 MEMORY 스토리지 엔진을 사용합니다.

MEMORY 스토리지는 일반적으로 사용되지 않기 때문에 그 특징을 간략하게 소개합니다. MEMORY 엔진 테이블 구조가 디스크에 생성되고 모든 데이터가 메모리에 배치되며 액세스 속도가 빠릅니다. 다시 시작하거나 시스템이 충돌하면 데이터가 사라지고 구조는 여전히 존재합니다.

# 创建普通表
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이 음수인 경우 위치는 오른쪽에서 왼쪽으로 계산됩니다.

세 번째 매개변수: 길이는 가로채야 하는 문자열의 길이를 나타냅니다. 쓰지 않으면 기본적으로 위치의 시작부터 마지막 ​​문자까지의 모든 문자가 가로채어집니다.

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) 삽입을 위한 저장 프로시저 생성 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;

입력 매개변수 n은 메모리 테이블에 삽입되는 데이터의 개수를 나타냅니다. memory_user_info

저장 프로시저 작업 스크린샷:

(2) 메모리 테이블 데이터를 생성하고 저장된 일반 테이블을 삽입합니다. 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;

가장 중요한 저장 프로시저이기도 합니다. 메모리 테이블의 주기적 삽입 및 삭제를 사용하여 일괄적으로 데이터를 생성합니다. mysql의 기본 max_heap_table_size 값을 변경할 필요가 없습니다. max_heap_table_size의 기능은 사용자가 생성하는 임시 메모리 테이블의 크기를 설정하는 것이며, 설정된 값이 클수록 더 많은 데이터를 메모리 테이블에 저장할 수 있습니다.

저장 프로시저 실행 스크린샷:

(3) 저장 프로시저 상태 확인

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

불분명한 쿼리 결과:

5 저장 프로시저 호출

mysql은 저장 프로시저 실행을 다음과 같이 호출합니다. call이므로 mysql 저장 프로시저를 실행하는 명령문은 CALL입니다. CALL은 저장 프로시저의 이름과 여기에 전달되어야 하는 매개변수를 허용합니다.

add_user_info 저장 프로시저를 호출하면 메모리 테이블 memory_user_info가 루프에 지속적으로 삽입된 다음 메모리 테이블에서 데이터를 가져와 일반 테이블 user_info에 삽입한 다음 메모리 테이블 데이터가 삭제되는 주기입니다. 루프가 끝날 때까지 계속됩니다. 100회 반복하여 매번 10,000개의 데이터를 생성하고 총 100만 개의 데이터를 생성합니다.

CALL add_user_info(100,10000);

6. 데이터 보기 및 검증

일반 테이블 데이터가 60,000개 항목에 도달하면 이미 약 23분 정도 소요됩니다. 이 시간을 기준으로 100만 개의 데이터를 생성하는 데 약 6시간이 소요될 것으로 추정됩니다. 시간이 많이 걸리는 점은 주로 필드 데이터를 무작위로 생성하는 4가지 기능에 있습니다. 필드 데이터에 무작위성이 필요하지 않으면 훨씬 더 빠릅니다.

데이터 기록 효과는 다음과 같습니다.

위 내용은 수백만 개의 데이터를 생성하기 위해 MySQL 저장 프로시저를 사용하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!