MySQL行使存储历程批量insert数据_手机照片强力数

日期:2014-07-13 / 人气: / 来源:网络

创建一个存储过程,名字为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);//
-----------------------------------------------------------  [[email protected] 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;//

------------------------------------------------------------------------------------------
[[email protected] 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

在MySQL存储过程中使用游标的方法

使用MYSQL存储过程,可以实现诸多的功能,下面将为您介绍一个MYSQL存储过程中使用游标的实例,希望对您能有所启迪。DELIMITER $$DROP PROCEDURE IF EXISTS getUserInfo $$CREATE PROCEDURE get

MySQL教程,存储过程,游标

作者:管理员




现在致电4006-2991-90 OR 查看更多联系方式 →

Go To Top 回顶部