创建测试表

mysql> show create table test_ignore\G
*************************** 1. row ***************************
       Table: test_ignore
Create Table: CREATE TABLE `test_ignore` (
  `id` int NOT NULL AUTO_INCREMENT,
  `column1` varchar(255) DEFAULT NULL,
  `column2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

测试普通insert into 循环10万次（存储过程一）

创建存储过程并调用

drop procedure insert_data_with_time;

DELIMITER //

CREATE PROCEDURE insert_data_with_time()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total_loops INT DEFAULT 100000;
    DECLARE start_time TIMESTAMP;
    DECLARE end_time TIMESTAMP;
    DECLARE time_diff FLOAT;
    -- 获取执行开始时间
    SET start_time = NOW();
    WHILE i <= total_loops DO

        -- 插入数据
        INSERT INTO test_ignore (column1, column2) VALUES ('xfafasdafqajfanaknfiojeiw29393939p2323mkfxxxxo0kfmdlx', 11341);
        -- 输出执行开始时间、结束时间和时间差
        SET i = i + 1;
    END WHILE;
 
        -- 获取执行结束时间
        SET end_time = NOW();
        -- 计算时间差（单位：秒）
        SET time_diff = TIMESTAMPDIFF(SECOND, start_time, end_time);
        SELECT CONCAT('Loop ', i, ': Start Time: ', start_time, ', End Time: ', end_time, ', Time Diff (seconds): ', time_diff);
END //

DELIMITER ;

CALL test.insert_data_with_time();

结果

mysql> CALL test.insert_data_with_time();
+------------------------------------------------------------------------------------------------------------------+
| CONCAT('Loop ', i, ': Start Time: ', start_time, ', End Time: ', end_time, ', Time Diff (seconds): ', time_diff) |
+------------------------------------------------------------------------------------------------------------------+
| Loop 100001: Start Time: 2023-07-17 20:22:38, End Time: 2023-07-17 20:23:02, Time Diff (seconds): 24             |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (24.29 sec)

Query OK, 0 rows affected (24.29 sec)

测试 insert into ignore 循环10万次（存储过程二）

drop procedure insert_data_with_time;

DELIMITER //

CREATE PROCEDURE insert_data_with_time()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total_loops INT DEFAULT 100000;
    DECLARE start_time TIMESTAMP;
    DECLARE end_time TIMESTAMP;
    DECLARE time_diff FLOAT;
    -- 获取执行开始时间
    SET start_time = NOW();
    WHILE i <= total_loops DO

        -- 插入数据
        INSERT ignore INTO test_ignore (id,column1, column2) VALUES (i,'xfafasdafqajfanaknfiojeiw29393939p2323mkfxxxxo0kfmdlx', 11341);

        SET i = i + 1;
    END WHILE;
 
        -- 获取执行结束时间
        SET end_time = NOW();
        -- 计算时间差（单位：秒）
        SET time_diff = TIMESTAMPDIFF(SECOND, start_time, end_time);
        SELECT CONCAT('Loop ', i, ': Start Time: ', start_time, ', End Time: ', end_time, ', Time Diff (seconds): ', time_diff);
END //

DELIMITER ;

CALL test.insert_data_with_time();

时间结果

mysql> CALL test.insert_data_with_time();
+------------------------------------------------------------------------------------------------------------------+
| CONCAT('Loop ', i, ': Start Time: ', start_time, ', End Time: ', end_time, ', Time Diff (seconds): ', time_diff) |
+------------------------------------------------------------------------------------------------------------------+
| Loop 100001: Start Time: 2023-07-17 20:29:13, End Time: 2023-07-17 20:29:14, Time Diff (seconds): 1              |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (1.22 sec)

Query OK, 0 rows affected (1.22 sec)

如果同样都执行第二个存储过程

mysql> CALL test.insert_data_with_time();
+------------------------------------------------------------------------------------------------------------------+
| CONCAT('Loop ', i, ': Start Time: ', start_time, ', End Time: ', end_time, ', Time Diff (seconds): ', time_diff) |
+------------------------------------------------------------------------------------------------------------------+
| Loop 100001: Start Time: 2023-07-17 20:34:09, End Time: 2023-07-17 20:34:34, Time Diff (seconds): 25             |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (25.57 sec)

Query OK, 0 rows affected (25.57 sec)

mysql> CALL test.insert_data_with_time();
+------------------------------------------------------------------------------------------------------------------+
| CONCAT('Loop ', i, ': Start Time: ', start_time, ', End Time: ', end_time, ', Time Diff (seconds): ', time_diff) |
+------------------------------------------------------------------------------------------------------------------+
| Loop 100001: Start Time: 2023-07-17 20:34:53, End Time: 2023-07-17 20:34:54, Time Diff (seconds): 1              |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (1.21 sec)

Query OK, 0 rows affected (1.21 sec)

成功1个，ignore1个(第三个过程)

drop procedure insert_data_with_time;

DELIMITER //

CREATE PROCEDURE insert_data_with_time()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total_loops INT DEFAULT 100000;
    DECLARE start_time TIMESTAMP;
    DECLARE end_time TIMESTAMP;
    DECLARE time_diff FLOAT;
    -- 获取执行开始时间
    SET start_time = NOW();
    WHILE i <= total_loops DO

        -- 插入数据
        INSERT ignore INTO test_ignore (id,column1, column2) VALUES (i,'xfafasdafqajfanaknfiojeiw29393939p2323mkfxxxxo0kfmdlx', 11341),(i+100000,'xxxxxx',2);

        SET i = i + 1;
    END WHILE;
 
        -- 获取执行结束时间
        SET end_time = NOW();
        -- 计算时间差（单位：秒）
        SET time_diff = TIMESTAMPDIFF(SECOND, start_time, end_time);
        SELECT CONCAT('Loop ', i, ': Start Time: ', start_time, ', End Time: ', end_time, ', Time Diff (seconds): ', time_diff);
END //

DELIMITER ;

CALL test.insert_data_with_time();

mysql> select max(id) from test_ignore;
+---------+
| max(id) |
+---------+
|  200000 |
+---------+
1 row in set (0.00 sec)

结论

下面测试都执行10万次