MySQL利用存储过程批量insert数据

创建一个存储过程，名字为autoInsert
CREATE PROCEDURE `autoInsert`()
BEGIN
    DECLARE i int default 10000;
    WHILE(i < 20000) DO
        insert into app.agent (account_id, firstname,lastname,password,enabled,sip_user,admin_password) values (10, 'sipp','testing',i,1,i,1234);
        SET i = i+1;
    END WHILE;
    END
调用存储过程
call autoInsert()
删除存储过程
DROP PROCEDURE IF EXISTS `autoInsert`

Delimiter ;// CREATE PROCEDURE restockPro(IN init INT, IN loop_time INT)     BEGIN     DECLARE Var INT;     DECLARE ID INT;     SET Var = 0;     SET ID = init;     WHILE Var < loop_time DO     INSERT INTO restock VALUES(ID, 1, 'R20108308054', '2010-08-30 00:00:00', '43', 'sm', 'dddd', 'sm', 'cccc', '', '', 'sa', 18860.00, 3000.00, 17000.00, 0, 3050, 2, 200, 20000.00, 18860.00, 5050, 2, 0, null, 0, 8, 8);     SET ID = ID + 1;     SET Var = Var + 1;     END WHILE;     END;// call restockPro(10000, 10000);//

-----------------------------------------------------------  [root@localhost root]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 6 to server version: 5.0.22-standard Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE Employee(EmployeeID int, EmployeeName char(30));
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Employee;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
mysql> Delimiter ;//
mysql> CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT)
    -> BEGIN
    -> DECLARE Var INT;
    -> DECLARE ID INT;
    -> SET Var = 0;
    -> SET ID = init;
    -> WHILE Var < loop_time DO
    -> INSERT INTO Employee(EmployeeID, EmployeeName) VALUES(ID, 'Garry');
    -> SET ID = ID + 1;
    -> SET Var = Var + 1;
    -> END WHILE;
    -> END;//
Query OK, 0 rows affected (0.00 sec)
mysql> call BatchInsert(1, 1000);
    -> //
    -> Aborted /* 怪我，忘了分隔符已改为;//，应该使用call BatchInsert(1, 1000);// 才对 */
------------------------------------------------------------------------------------------
 Delimiter ;//
CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT)
BEGIN
DECLARE Var INT;
DECLARE ID INT;
SET Var = 0;
SET ID = init;
WHILE Var < loop_time DO
INSERT INTO Employee(EmployeeID, EmployeeName) VALUES(ID, 'Garry');
SET ID = ID + 1;
SET Var = Var + 1;
END WHILE;
END;//

------------------------------------------------------------------------------------------
[root@localhost root]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 7 to server version: 5.0.22-standard Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select count(*) from Employee;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
mysql> call BatchInsert(1, 1000);
Query OK, 1 row affected (0.03 sec)
mysql> select count(*) from Employee;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------------
来自ITPUB的一位老兄。写的很好。贴出来。 DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`sp_insert_batch`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_batch`(IN number int(11))
BEGIN
  declare i int(11);
  set i = 1;
  -- such as 1-2000,2000-4000,....
  WHILE i <= number DO
    if mod(i,2000)=1 then
       set @sqltext =concat('(''',concat('t',i),''',''',now(),''',',ceil(10*rand()),')');
    elseif mod(i,2000)=0 then
       set @sqltext=concat(@sqltext,',(''',concat('t',i),''',''',now(),''',',ceil(10*rand()),')');
       set @sqltext=concat('insert into song (name,datetime,rank) values',@sqltext);
       prepare stmt from @sqltext;
       execute stmt;
       DEALLOCATE PREPARE stmt;
       set @sqltext='';
    else
       set @sqltext=concat(@sqltext,',(''',concat('t',i),''',''',now(),''',',ceil(10*rand()),')');
    end if;
    set i = i + 1;
  END WHILE;
  -- process when number is not be moded by 2000
  -- such as 2001,4002,15200,...
  if @sqltext<>'' then
     set @sqltext=concat('insert into song (name,datetime,rank) values',@sqltext);
     prepare stmt from @sqltext;
     execute stmt;
     DEALLOCATE PREPARE stmt;
     set @sqltext='';
  end if;
END$$
DELIMITER ;
附表结构。

/*DDL Information For - test.song*/
-----------------------------------

Table Create Table
------ ----------------------------------------------------------------------------------------
song CREATE TABLE `song` (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Autoincreament element',
          `name` text NOT NULL,
          `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          `rank` int(11) NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=MyISAM AUTO_INCREMENT=8102001 DEFAULT CHARSET=gbk

声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。