Inserting multiple rows in mysql

Learn inserting multiple rows in mysql with practical examples, diagrams, and best practices. Covers mysql, sql-insert development techniques with visual explanations.
Tags:
Categories:

Efficiently Inserting Multiple Rows in MySQL

Efficiently Inserting Multiple Rows in MySQL

Learn various methods to insert multiple rows into a MySQL database, optimizing performance and understanding best practices for different scenarios.

Inserting data into a database is a fundamental operation. While inserting a single row is straightforward, inserting multiple rows efficiently is crucial for application performance, especially when dealing with large datasets. This article explores several techniques for bulk insertion in MySQL, ranging from basic SQL statements to more advanced programmatic approaches, and discusses their respective advantages and disadvantages.

1. Using a Single INSERT Statement with Multiple VALUES Clauses

The most common and often most efficient way to insert multiple rows in MySQL is to use a single INSERT statement with multiple VALUES clauses. This method significantly reduces the overhead associated with establishing connections, parsing queries, and transaction management, as the database only needs to process one command.

INSERT INTO products (name, price, stock)
VALUES
    ('Laptop', 1200.00, 50),
    ('Mouse', 25.00, 200),
    ('Keyboard', 75.50, 150);

A single INSERT statement inserting three rows into the 'products' table.

2. Using Multiple INSERT Statements (Less Efficient)

While possible, executing individual INSERT statements for each row is the least efficient method for bulk insertion. Each statement incurs the full overhead of query parsing and execution. This approach is typically only suitable for very small numbers of rows or when data arrives one row at a time and cannot be batched.

INSERT INTO products (name, price, stock) VALUES ('Monitor', 300.00, 70);
INSERT INTO products (name, price, stock) VALUES ('Webcam', 50.00, 100);
INSERT INTO products (name, price, stock) VALUES ('Headphones', 100.00, 80);

Separate INSERT statements for each row, demonstrating lower efficiency.

3. Using LOAD DATA INFILE (Highly Efficient for Files)

For inserting a very large number of rows from a file (e.g., CSV, TSV), the LOAD DATA INFILE statement is by far the most efficient method. It is optimized for bulk data loading and bypasses much of the SQL parsing overhead. This is ideal for importing data from external sources.

LOAD DATA INFILE '/path/to/your/data.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Loading data from a CSV file into the 'products' table, ignoring the header row.

A flowchart diagram illustrating the decision process for choosing a MySQL multi-row insert strategy. Start with 'Need to insert multiple rows?'. If 'Yes', then 'Is data in a file?'. If 'Yes' to file, use 'LOAD DATA INFILE'. If 'No' to file, then 'Small number of rows (e.g., < 1000)?'. If 'Yes', use 'Single INSERT with multiple VALUES'. If 'No' (large number of rows), use 'Batching with prepared statements'. Blue rectangles for actions, green diamonds for decisions, and arrows showing the flow.

Decision Flow for MySQL Multi-Row Insert Strategies

4. Programmatic Batching with Prepared Statements

When dealing with dynamic data generated by an application, and the number of rows is too large for a single INSERT ... VALUES statement, programmatic batching with prepared statements is the best approach. This involves preparing the INSERT statement once and then executing it multiple times with different sets of parameters, typically within a transaction. This balances efficiency with flexibility.

Tab 1

import mysql.connector

# Database connection details
config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

try:
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()

    # Data to insert
    data_to_insert = [
        ('Desk', 150.00, 30),
        ('Chair', 80.00, 120),
        ('Lamp', 45.00, 200),
        ('Bookshelf', 110.00, 60)
    ]

    # Prepare the INSERT statement
    sql = "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)"

    # Execute in batches within a transaction
    conn.start_transaction()
    try:
        cursor.executemany(sql, data_to_insert)
        conn.commit()
        print(f"{cursor.rowcount} rows were inserted.")
    except mysql.connector.Error as err:
        conn.rollback()
        print(f"Error: {err}")

except mysql.connector.Error as err:
    print(f"Connection Error: {err}")
finally:
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL connection is closed.")

Tab 2

<?php

$servername = "localhost";
$username = "your_user";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Data to insert
$data_to_insert = [
    ['Desk', 150.00, 30],
    ['Chair', 80.00, 120],
    ['Lamp', 45.00, 200],
    ['Bookshelf', 110.00, 60]
];

// Prepare the INSERT statement
$stmt = $conn->prepare("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)");
$stmt->bind_param("sdi", $name, $price, $stock); // s=string, d=double, i=integer

$conn->begin_transaction();

try {
    foreach ($data_to_insert as $row) {
        $name = $row[0];
        $price = $row[1];
        $stock = $row[2];
        $stmt->execute();
    }
    $conn->commit();
    echo "New records created successfully";
} catch (mysqli_sql_exception $exception) {
    $conn->rollback();
    throw $exception;
}

$stmt->close();
$conn->close();

?>

Tab 3

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class BulkInsert {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_user";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        List<List<Object>> dataToInsert = Arrays.asList(
            Arrays.asList("Desk", 150.00, 30),
            Arrays.asList("Chair", 80.00, 120),
            Arrays.asList("Lamp", 45.00, 200),
            Arrays.asList("Bookshelf", 110.00, 60)
        );

        String sql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            conn.setAutoCommit(false); // Start transaction

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                final int batchSize = 100;
                int count = 0;

                for (List<Object> row : dataToInsert) {
                    pstmt.setString(1, (String) row.get(0));
                    pstmt.setDouble(2, (Double) row.get(1));
                    pstmt.setInt(3, (Integer) row.get(2));
                    pstmt.addBatch();

                    if (++count % batchSize == 0) {
                        pstmt.executeBatch(); // Execute batch
                    }
                }
                pstmt.executeBatch(); // Execute remaining batch
                conn.commit(); // Commit transaction
                System.out.println("Bulk insert successful.");
            } catch (SQLException e) {
                conn.rollback(); // Rollback on error
                System.err.println("SQL Error during batch insert: " + e.getMessage());
            }
        } catch (SQLException e) {
            System.err.println("Connection Error: " + e.getMessage());
        }
    }
}

Conclusion and Best Practices

Choosing the right method for inserting multiple rows in MySQL depends on the source of your data, the volume, and performance requirements. For data originating from files, LOAD DATA INFILE is unparalleled. For application-generated data, a single INSERT statement with multiple VALUES clauses is excellent for moderate numbers of rows. For very large, dynamic datasets, programmatic batching with prepared statements within a transaction offers the best balance of control and efficiency. Always consider the max_allowed_packet setting and use transactions to maintain data integrity.