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

最新推荐文章于 2024-09-05 20:26:16 发布

转载最新推荐文章于 2024-09-05 20:26:16 发布 · 8.7k 阅读

文章标签：

#mysql #存储 #table #query #server #database

Linux 同时被 2 个专栏收录

50 篇文章

订阅专栏

DataBase

16 篇文章

订阅专栏

本文演示了如何使用MySQL存储过程实现数据批量插入操作。通过定义存储过程`BatchInsert`，可在指定范围内快速填充数据库表。示例中创建了一个名为`Employee`的表，并通过调用存储过程成功插入了1000条记录。

[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)