使用存储过程向MySQL批量插入数据

 [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);// 才对 */
[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)