SQL批量插入

概述

在开发数据库应用程序时，经常需要批量插入数据。如果逐条插入数据，会导致插入大量的SQL语句，降低性能。相反，使用批量插入可以在一次数据库交互中插入多条数据，提高效率。本文将详细介绍SQL批量插入的概念、使用方法和示例代码。

批量插入的优势

使用SQL批量插入相较于逐条插入的优势主要体现在以下几个方面：
1. 降低数据库交互次数：逐条插入将产生大量的数据库交互，而批量插入可以在一次数据库连接中完成多条插入，减少了频繁的网络交互，提高了效率。
2. 减少日志记录：数据库在插入一条记录时，会记录日志。批量插入只需要记录一次日志，相较于逐条插入，减少了日志记录的开销。
3. 提高数据一致性：在批量插入过程中，所有的插入语句会在一个事务中执行，要么全部插入成功，要么全部失败。保证了数据的一致性。

SQL批量插入方法

在不同的数据库管理系统中，SQL批量插入的方法可能略有不同。下面将分别介绍在MySQL和Oracle数据库中的SQL批量插入方法。

MySQL中的SQL批量插入

在MySQL中，可以使用INSERT INTO ... VALUES语句来进行批量插入。语法如下：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1, value2, value3, ...),
    (value1, value2, value3, ...),
    ...
    (value1, value2, value3, ...);

其中，table_name为目标表名，column1, column2, column3, ...为目标表中的列名列表，value1, value2, value3, ...为要插入的数据值。每个括号内表示一条数据。通过在一次SQL语句中多次使用括号，可以实现批量插入多条数据。

例如，如果要将以下数据批量插入到employees表中：

| id  | name   | age | sex  |
| --- | ------ | --- | ---- |
| 1   | Alice  | 25  | Female |
| 2   | Bob    | 30  | Male   |
| 3   | Carol  | 28  | Female |

可以使用以下SQL语句：

INSERT INTO employees (id, name, age, sex)
VALUES
    (1, 'Alice', 25, 'Female'),
    (2, 'Bob', 30, 'Male'),
    (3, 'Carol', 28, 'Female');

Oracle中的SQL批量插入

在Oracle中，可以使用INSERT ALL语句来实现批量插入。语法如下：

INSERT ALL
    INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...)
    INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...)
    ...
SELECT 1 FROM DUAL;

其中，table_name为目标表名，column1, column2, column3, ...为目标表中的列名列表，value1, value2, value3, ...为要插入的数据值。每个INTO子句表示一条数据。通过在一次SQL语句中多次使用INTO子句，可以实现批量插入多条数据。最后的SELECT 1 FROM DUAL;语句表示向数据库返回一个一行一列的结果集。

例如，要将以下数据批量插入到employees表中：

| id  | name   | age | sex  |
| --- | ------ | --- | ---- |
| 1   | Alice  | 25  | Female |
| 2   | Bob    | 30  | Male   |
| 3   | Carol  | 28  | Female |

可以使用以下SQL语句：

INSERT ALL
    INTO employees (id, name, age, sex) VALUES (1, 'Alice', 25, 'Female')
    INTO employees (id, name, age, sex) VALUES (2, 'Bob', 30, 'Male')
    INTO employees (id, name, age, sex) VALUES (3, 'Carol', 28, 'Female')
SELECT 1 FROM DUAL;

SQL批量插入示例代码

下面将分别以MySQL和Oracle数据库为例，给出SQL批量插入的示例代码。

MySQL示例代码

假设有一个名为students的表，结构如下：

| id  | name   | age | sex  |
| --- | ------ | --- | ---- |
| int | string | int | string |

现在需要将以下数据批量插入到students表中：

| id  | name    | age | sex   |
| --- | ------- | --- | ----- |
| 1   | Alice   | 18  | Female |
| 2   | Bob     | 20  | Male   |
| 3   | Carol   | 19  | Female |
| 4   | David   | 21  | Male   |
| 5   | Emily   | 22  | Female |

可以使用以下Python代码实现：

import mysql.connector

# 连接数据库
cnx = mysql.connector.connect(user='root', password='your_password',
                              host='127.0.0.1',
                              database='your_database')
cursor = cnx.cursor()

# 定义要批量插入的数据
data = [
    (1, 'Alice', 18, 'Female'),
    (2, 'Bob', 20, 'Male'),
    (3, 'Carol', 19, 'Female'),
    (4, 'David', 21, 'Male'),
    (5, 'Emily', 22, 'Female')
]

# 执行批量插入
insert_stmt = "INSERT INTO students (id, name, age, sex) VALUES (%s, %s, %s, %s)"
cursor.executemany(insert_stmt, data)

# 提交事务
cnx.commit()

# 关闭数据库连接
cursor.close()
cnx.close()

以上代码使用Python的mysql.connector模块连接MySQL数据库，并使用executemany方法执行批量插入。

Oracle示例代码

假设有一个名为students的表，结构如下：

| id  | name   | age | sex  |
| --- | ------ | --- | ---- |
| int | string | int | string |

现在需要将以下数据批量插入到students表中：

import cx_Oracle

# 连接数据库
dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='your_service_name')
connection = cx_Oracle.connect(user='your_username', password='your_password', dsn=dsn_tns)
cursor = connection.cursor()

# 定义要批量插入的数据
data = [
    (1, 'Alice', 18, 'Female'),
    (2, 'Bob', 20, 'Male'),
    (3, 'Carol', 19, 'Female'),
    (4, 'David', 21, 'Male'),
    (5, 'Emily', 22, 'Female')
]

# 执行批量插入
insert_stmt = "INSERT ALL INTO students (id, name, age, sex) VALUES (:1, :2, :3, :4)"
cursor.executemany(insert_stmt, data)

# 提交事务
connection.commit()

# 关闭数据库连接
cursor.close()
connection.close()

以上代码使用Python的cx_Oracle模块连接Oracle数据库，并使用executemany方法执行批量插入。