【MySql】mysql利用存储过程批量插入数据

爱智能

最近需要测试一下mysql单表数据达到1000W条以上时增删改查的性能。由于没有现成的数据，因此自己构造，本文只是实例，以及简单的介绍。

首先当然是建表：

1. CREATE TABLE `fortest` (
   
2.   `ID` INT(30) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   
3.   `IP` VARCHAR(32) NOT NULL,
   
4.   `OID` VARCHAR(15) DEFAULT NULL)

复制代码

其次，构建存储过程：

1. DELIMITER $
   
2. 
   
3. USE `插入表所在的数据库名字`$
   
4. 
   
5. DROP PROCEDURE IF EXISTS `autoinsert`$
   
6. 
   
7. CREATE DEFINER=`root`@`192.168.137.10` PROCEDURE `autoinsert`(IN IP_NUM INT, IN OID_NUM INT)
   
8. BEGIN
   
9.   DECLARE iIP INT DEFAULT 0 ;
   
10.   DECLARE iOID INT DEFAULT 0 ;   
    
11.    WHILE(iIP < IP_NUM)
    
12.     DO
    
13.       SET iOID = 0;
    
14.       WHILE(iOID<OID_NUM)
    
15.       DO
    
16.         SET @mySql=CONCAT("INSERT INTO fortest (IP, OID) VALUES(CONCAT((ROUND(RAND() * 255) + 1),'.',(ROUND(RAND() * 255) + 1),'.',(ROUND(RAND() * 255) + 1),'.',(ROUND(RAND() * 255) + 1)),ROUND(RAND()*100)+1);");            
    
17.         PREPARE stmt FROM @mySql;
    
18.         EXECUTE stmt;  
    
19.         DEALLOCATE PREPARE stmt;
    
20.         SET iIP = iIP+1;
    
21.       END WHILE;
    
22.         SET iPC = iPC+1;
    
23.     END WHILE;
    
24. 
    
25.     END$
    
26. 
    
27. DELIMITER ;

复制代码

上述存储过程指定了两个输入参数：IP_NUM OID_NUM，两个参数分别指定了有多少台机器，以及每台机器有多少OID。

之后调用存储过程就可以了：

1. call autoinsert 1000 50

复制代码

意思是，有100台机器，每个机器有50个参数。

这样，我们就构建了50000条数据，如果按上述存储过程，想达到1000W的数据，还是要花点时间的。可以采用如下方法，进一步提高速度：

首先创建具有同样表结构的表：

1. CREATE TABLE fortest_2 LIKE fortest;

复制代码

然后根据fortest表插入5W条数据

1. INSERT INTOfortest_2(IP,OID) SELECT IP,ROUND(RAND() * 100) + 1) FROM fortest;
   

复制代码

上述一条语句执行速度特别快，瞬间就插入了5W条数据。可以写个脚本执行：

1. #!/bin/bash
   
2. 
   
3. i=1;
   
4. MAX_INSERT_ROW_COUNT=$1;
   
5. j=0;
   
6. while [ $i -le $MAX_INSERT_ROW_COUNT ]
   
7. do
   
8.     time mysql -h192.168.137.1 -uroot -p123456 fortest -e "INSERT INTOfortest_2(IP,OID) SELECT IP,ROUND(RAND() * 100) + 1) FROM fortest;"
   
9.     echo "INSERT $i "   
   
10.     i=$(($i+1))
    
11. #    sleep 0.05
    
12. done
    
13. 
    
14. exit 0

复制代码