mysql - MySQL使用触发器并行插入多个表

问题描述

我正在尝试将大量数据（500M 行）插入 MySQL。我将整行放入第一个表中，并在触发器后使用插入。由于这会将数据插入 7 个不同的表（出于规范化目的），我想知道是否可以同时进行 7 个插入。目前我正在使用 MySQL 5.1，它不允许我在同一张表上创建更多触发器。你能给我一些如何提高插入速度的建议吗：

CREATE TRIGGER temptable_tr AFTER INSERT ON temp
    FOR EACH ROW
    BEGIN
        DECLARE TR_00 INT;
        DECLARE TR_1G INT;
        DECLARE TR_2G INT;
        DECLARE TR_3G INT;
        DECLARE TR_1S INT;
        DECLARE TR_2S INT;
        DECLARE TR_3S INT;     
        /* This part repeats 7 times, instead of 00, we can put 1G, 2G, 3G, 1S, 2S, 3S
         Since these 7 parts insert into seven independent tables, would it possible to run them parallely?*/
        BEGIN
            DECLARE T_MD5_00 CHAR(32);
            DECLARE SBN_00 INT;
            DECLARE S_MD5_00 CHAR(32);
            DECLARE SBS_MD5_00 VARCHAR(500);

            SET T_MD5_00 = SELECT MD5(NEW.tr_00) FROM DUAL;
            SET TR_00 = (SELECT tr_00_id FROM trans_00 WHERE trans_00_MD5 = T_MD5_00);
            SET SGN_00 = REPLACE( substring(NEW.tr_00,1,((select LOCATE('>', NEW.tr_00)-1)))  ,'[*:1]','[*]') ;
            SET S_MD5_00 = SELECT MD5(SGN_00) FROM DUAL;
            SET SBN_00 = (SELECT subs_00_id FROM substr_00 WHERE subs_00_MD5 = S_MD5_00);

            IF (TR_00 IS NULL OR SBN_00 IS NULL) THEN
                IF (TR_00 IS NULL) THEN
                    INSERT INTO trans_00(transform_00) values(NEW.tr_00);
                    INSERT INTO trans_00(trans_00_MD5) values(T_MD5_00);
                    SET TR_00 = LAST_INSERT_ID();
                END IF;
                IF (SBN_00 IS NULL) THEN
                    INSERT INTO substr_00(substruct00,subs_00_MD5) values(SGN_00,S_MD5_00);
                    SET SBN_00 = LAST_INSERT_ID();
                END IF;
                INSERT INTO tr_subs_00(tr_00_id,subs_00_id) VALUES(TR_00,SBN_00);
            END IF;
        END;
        .....
        /* At the end collect the TR_?? ids and insert them into an other table:*/
        INSERT INTO pairs(tr_00_id,tr_1G_id,tr_2G_id,tr_3G_id,tr_1S_id,tr_2S_id,tr_3S_id) VALUES(TR_00,TR_1G,TR_2G,TR_3G,TR_1S,TR_2S,TR_3S
    END;

标签： mysqltriggersdatabase-normalization

mysql - MySQL使用触发器并行插入多个表

问题描述

解决方案

推荐阅读