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

測试一下mysql单表数据达到1000W条以上时增删改查的性能。因为没有现成的数据，因此自己构造

--表结构
CREATE TABLE `fortest` (
  `ID` INT(30) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `IP` VARCHAR(32) NOT NULL,
  `OID` VARCHAR(15) DEFAULT NULL);

    --存储过程
delimiter $$
        CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert`(IN IP_NUM INT, IN OID_NUM INT)
BEGIN
  DECLARE iIP INT DEFAULT 0 ;
  DECLARE iOID INT DEFAULT 0 ;
   WHILE(iIP < IP_NUM)
    DO
      SET iOID = 0;
      WHILE(iOID<OID_NUM)
      DO
        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);");
        PREPARE stmt FROM @mySql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET iOID = iOID+1;
      END WHILE;
     SET iIP = iIP+1;
    END WHILE;
  END$$
DELIMITER ;

call autoinsert(10,10);


--原文有点麻烦，多了个循环,这是自己写的
delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert2`(IN IP_NUM INT)
begin
declare iIP int default 0;
while(iIP<IP_NUM)
do
 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);");
 PREPARE stmt FROM @mySql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET iIP = iIP+1;
 end while;
 end $$
 delimiter ;