MySQL如何快速导入大量数据

这里有一张测试表：

create table person
(
    id       integer primary key,
    name     varchar(100) null,
    password varchar(100) null,
    email    varchar(100) null,
    created  timestamp    null,
    notes    varchar(200) null
)

存储过程

DELIMITER $$

CREATE PROCEDURE insert_persons(IN total_records INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE random_name VARCHAR(100);
    DECLARE random_password VARCHAR(100);
    DECLARE random_email VARCHAR(100);
    DECLARE random_notes VARCHAR(200);

    START TRANSACTION;
    WHILE i < total_records
        DO
            SET random_name = CONCAT('name_', i);
            SET random_password = CONCAT('pass_', i);
            SET random_email = CONCAT('email_', i, '@example.com');
            SET random_notes = CONCAT('note_', i);

            INSERT INTO person (id, name, password, email, created, notes)
            VALUES (i, random_name, random_password, random_email, NOW(), random_notes);

            SET i = i + 1;

            -- 每10000条提交一次事务，防止事务太大影响性能
            IF i % 10000 = 0 THEN
                COMMIT;
                START TRANSACTION;
            END IF;
        END WHILE;
    COMMIT;
END$$

DELIMITER ;

CALL insert_persons(1000000);

select  count(*) from person;

大概30s可以插入100万数据