·

photo credit: hermzz via photopin cc

Converting multiple MySQL INSERTS into a single statement

Tags: , ,

So I had to import this huge MySQL database the other time and I had the problem that each INSERT -statement only contained 1 row of data. Now, for a small database this is not a big deal, but for a database that’s 1GB or more importing all these separate rows takes forever!
So i had to fix it …

PHP CLI to the rescue!

I am a big fan of PHP as a CLI language: creating shell scripts in a language that you’re good at really helps a lot. That’s why I decided to create a simple PHP-script that would iterate through each line of the MySQL file and concatenate the multiple INSERT -statements into multiple separate INSERT -statement, each one containing 1000 rows.
The result? Well, it sure made the import faster! What otherwise would take several hours or even a day (or more), was now done in a couple of minutes! And because I like to share, I share this script with you:

Converting multiple INSERT-statements into a single one

Disclaimer

This script worked in my situation where the structure of the SQL file that was provided to me was pretty much set:

  • Each importing of a table started with a DROP TABLE IF NOT EXISTS -statement.
  • The SQL file only contained INSERT -statements.
  • Haven’t tested with ENUM -datatypes.
  • At the end of each ‘table block’ there is a UNLOCK TABLES -statement.

If your source-SQL-file does not match any or some of these rules, you might have to edit this script to fit your needs.

Visitors give this article an average rating of 4.0 out of 5.

How would you rate this article?

2 thoughts on “Converting multiple MySQL INSERTS into a single statement”

  1. marcobax22 says:

    One sidenote: when converting multiple INSERT statements into one big query, keep in mind the maximum size of one communication packet. The largest communication packet ( single query ) that can be send to the MySQL server is 1 gigabyte. While you probably will never write a single SQL query that is of this size you must know that by default most servers have this setting at a default value of 1 megabyte.

    If you do run into memory problems, you can increase this value by using the Mysql client program and running this command: mysql –max_allowed_packet=32M By default the mysql client program uses 16 megabyte of maximum packet size.

    The server-wide default value for max_allowed_packet is 1 megabyte, increase this number by adding the below configuration to your MySQL server configuration file:

    [mysqld]
    max_allowed_packet=32M

    I recommend resetting this value when you’re done importing a large dataset.

    1. Giel Berkers says:

      Thanks marco for this great tip. Didn’t know that one!

Leave a Reply