存储过程 + insert into values (),(),();插入海量数据

一时失言乱红尘 2022-05-03 06:58 147阅读 0赞

发现该文存储过程中使用 `insert into values` 插入数据，但是每次只插入一条数据，因此，决定一次插入十条观察效率

> 参考文章 ：[重新学习MySQL数据库12：从实践sql语句优化开始][MySQL_12_sql] \-> `自己写的海量数据sql优化实践`

**测试环境**： Mysql 5.7, Navicat 12.0.18  
**插入10万条数据结果**：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpX3NodXlpbmd6aGl4aWE_size_27_color_FFFFFF_t_70]  
*其中* set global general\_log = on 用于开启日志，off 用于关闭日志。 general\_log 将所有到达MySQL Server的SQL语句记录下来。

**代码如下**

-- 创建插入数据的存储过程
    DROP PROCEDURE IF EXISTS `add_vote_record_memory`;
    DELIMITER //
    CREATE PROCEDURE `add_vote_record_memory`(IN n INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
        WHILE i < n DO
            INSERT INTO `vote_record_memory` 
    						VALUES
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW()),
    							(NULL, rand_string(20), FLOOR(1 + RAND() * 10000), FLOOR(0 + RAND()*3), FLOOR(1 + RAND()*2), NOW());							
            SET i = i + 10;
        END WHILE;
    END //
    DELIMITER ;  -- 改回默认的 MySQL delimiter：';'

站在巨人的肩膀上，才能尿得更远。。。

[MySQL_12_sql]: https://blog.csdn.net/a724888/article/details/79394168#t1
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpX3NodXlpbmd6aGl4aWE_size_27_color_FFFFFF_t_70]: /images/20220503/bb13d006f78648c78aab310eba516112.png