Comment utiliser les procédures stockées Mysql pour créer des millions de données

1. Préparation

(1) Étant donné que des procédures stockées sont utilisées, MySQL prend en charge les procédures stockées à partir de la version 5.0, la version de MySQL doit donc être 5.0 ou supérieure. Comment vérifier la version de MySQL, utilisez l'instruction SQL suivante pour vérifier :

(2) Créez deux tables avec la même structure de table, mais utilisez des moteurs de stockage différents, comme indiqué ci-dessous, les tables ordinaires utilisent mysql5.5 version par défaut Le moteur de stockage INNODB et la table mémoire utilisent le moteur de stockage MEMORY.

Le stockage MEMORY n'étant pas couramment utilisé, voici une brève introduction à ses caractéristiques : la structure de la table du moteur MEMORY est créée sur le disque, toutes les données sont placées dans la mémoire, et la vitesse d'accès est cependant rapide lorsque MySQL l'est. redémarré ou une fois que le système plante, les données disparaîtront, la structure existe toujours.

# 创建普通表
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. Principales étapes de mise en œuvre

(1) Créer une fonction qui génère automatiquement des données et l'utiliser lors de l'insertion ;

(2) Créer une procédure de stockage de données à insérer dans la table mémoire et appeler la fonction de génération de données créée ;

(3) Créez les données de la table mémoire et insérez-les dans la procédure stockée de la table ordinaire ;

(4) Appelez la procédure stockée.

(5) Visualisation et vérification des données

3. Créez une fonction qui génère automatiquement des données

(1) Générez n nombres aléatoires

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;

Capture d'écran de la fonction en cours d'exécution :

Les fonctions MySQL utilisées dans le script et leurs fonctions sont les suivantes :

a.concat() : Concatène plusieurs chaînes en une seule chaîne.

b.Floor() : arrondir à l'inférieur.

c.substring(string, position, length)

Le premier paramètre : string fait référence à la chaîne d'origine qui doit être interceptée.

Le deuxième paramètre : position fait référence à la position à partir de laquelle intercepter la sous-chaîne. Le numéro de séquence de codage de position du caractère commence ici à 1. Si la position est un nombre négatif, la position est comptée de droite à gauche.

Le troisième paramètre : la longueur fait référence à la longueur de la chaîne qui doit être interceptée. S'il n'est pas écrit, tous les caractères depuis le début de la position jusqu'au dernier caractère seront interceptés par défaut.

d.RAND() : ne peut générer que des décimales aléatoires comprises entre 0 et 1.

(2) Créer une fonction pour générer aléatoirement des numéros de téléphone portable

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;

Capture d'écran de la fonction en cours d'exécution :

(3) Créer une fonction de nom d'utilisateur générée aléatoirement

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;

Capture d'écran de la fonction en cours d'exécution :

(4) Générer aléatoirement la fonction de statut des utilisateurs

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;

Capture d'écran de la fonction en cours d'exécution :

(5) Afficher toutes les informations sur les fonctions personnalisées dans la base de données

4 Créer une procédure stockée

(1) Créer une procédure stockée pour l'insertion. données dans la table mémoire

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;

Le paramètre d'entrée n indique combien de données sont insérées dans la table mémoire memory_user_info

Capture d'écran de l'opération de procédure stockée :

(2) Créer des données de table mémoire et insérer une table ordinaire stockée procédure

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;

C'est la procédure stockée la plus importante. C'est aussi l'entrée. Elle utilise l'insertion et la suppression cycliques de tables mémoire pour générer des données par lots. Il n'est pas nécessaire de modifier la valeur par défaut de mysql (la valeur par défaut est). 16M). La fonction de max_heap_table_size est de configurer la taille de la table de mémoire temporaire créée par l'utilisateur. Plus la valeur configurée est élevée, plus elle est grande, plus de données peuvent être stockées dans la table de mémoire.

Capture d'écran de la procédure stockée en cours d'exécution :

(3) Vérifiez l'état de la procédure stockée

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

Résultats de requête flous :

5. Appeler la procédure stockée

mysql appelle l'exécution de la procédure stockée en tant que procédure stockée. appelez, donc mysql L'instruction pour exécuter la procédure stockée est CALL. CALL accepte le nom de la procédure stockée et tous les paramètres qui doivent lui être transmis.

En appelant la procédure stockée add_user_info, la table mémoire memory_user_info est insérée en continu dans une boucle, puis les données sont obtenues à partir de la table mémoire et insérées dans la table ordinaire user_info, puis les données de la table mémoire sont supprimées, et ce cycle continue jusqu'à la fin de la boucle. Bouclez 100 fois, générant 10 000 éléments de données à chaque fois, et un total de 1 million d'éléments de données.

CALL add_user_info(100,10000);

6. Visualisation et vérification des données

Lorsque les données d'une table ordinaire atteignent 60 000 éléments, cela prend déjà environ 23 minutes. Sur la base de ce temps, on estime qu'il faudra environ 6 heures pour générer 1 million de données. Le point qui prend du temps réside principalement dans les quatre fonctions qui génèrent de manière aléatoire des données de terrain. Si les données de terrain ne nécessitent pas de caractère aléatoire, elles seront beaucoup plus rapides.

L'effet d'enregistrement des données est le suivant :

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!