How Can I Efficiently Insert Large Numbers of Rows into a MySQL Database?

Efficient Row Insertion in MySQL Databases

Inserting large numbers of rows into a MySQL database can be a time-consuming task, especially when using traditional methods like single-row inserts. To enhance performance, consider employing more efficient techniques.

Single-Row Insert vs. Bulk Inserts

Typically, single-row inserts are used to insert individual records one at a time. While this approach is straightforward, it can become inefficient when dealing with large datasets. Instead, consider using bulk inserts to insert multiple rows simultaneously. By concatenating multiple row values into a single INSERT statement, significant time savings can be achieved.

Code Implementation

The following code demonstrates a bulk insert approach that can significantly improve insertion speed:

public static void BulkToMySQL()
{
    string ConnectionString = "server=192.168.1xxx";
    StringBuilder sCommand = new StringBuilder("INSERT INTO User (FirstName, LastName) VALUES ");
    using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
    {
        List<string> Rows = new List<string>();
        for (int i = 0; i < 100000; i++)
        {
            Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString("test"), MySqlHelper.EscapeString("test")));
        }
        sCommand.Append(string.Join(",", Rows));
        sCommand.Append(";");
        mConnection.Open();
        using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
        {
            myCmd.CommandType = CommandType.Text;
            myCmd.ExecuteNonQuery();
        }
    }
}

This code iterates over the rows to insert and constructs a bulk INSERT statement by concatenating the individual row values. The resulting statement inserts all rows simultaneously, leading to substantial performance gains.

Escaping Input Data for Security

It's crucial to note that bulk inserts can expose potential security vulnerabilities. To prevent SQL injection, it's essential to escape any user input before including it in the INSERT statement. As demonstrated in the code above, MySqlHelper.EscapeString can be used for this purpose.

The above is the detailed content of How Can I Efficiently Insert Large Numbers of Rows into a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!