Bluk Insert

 

Bluk Insert :

Using Bulk Insert Statement in MySQL. The INSERT statement in MySQL also supports the use of VALUES syntax to insert multiple rows as a bulk insert statement. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

Using Bulk Insert Statement

The INSERT statement in MySQL also supports the use of VALUES syntax to insert multiple rows as a bulk insert statement.

To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

The beauty of this lies in its ability to insert multiple records into MySQL in a single insert statement which is by default in a single transaction, and as such inserting these records either succeed or fail all together. This saves us from coding multiple insert statements and making multiple round trips to MySQL server for inserts.

 

For eaxmple, below is a bulk insert statement for the shippers table in our popular Northwind database.

 

insert into `shippers`(`ShipperID`,`CompanyName`,`Phone`) values 
(1,'Speedy Express','(503) 555-9831'),
(2,'United Package','(503) 555-3199'),
(3,'Federal Shipping','(503) 555-9931');

 

Comparing to the following 3 SQL scripts, the bulk insert statement is more efficient.

 

insert into `shippers`(`ShipperID`,`CompanyName`,`Phone`) 
values(1,'Speedy Express','(503) 555-9831');
 
insert into `shippers`(`ShipperID`,`CompanyName`,`Phone`) 
values(2,'United Package','(503) 555-3199');
 
insert into `shippers`(`ShipperID`,`CompanyName`,`Phone`) 
values(3,'Federal Shipping','(503) 555-9931');

 

Configure max_allowed_packet

One practical example of using bulk insert statement is shown in this article How to load IP addresses for countries into your MySQL database. The IPCountry table is exported as SQL bulk insert statement and it’s automatically split into multiple lines of bulk insert statements for a good reason – limit the size of each insert statement within the max_allowed_packet.

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. (A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.)

 

If you have a couple hundred thousand rows to insert, don’t code them into one single bulk insert statement. Otherwise, most likely you’ll get error of exceeding the Max Allowed Packet size in MySQL – Packets larger than max_allowed_packet are not allowed.

 

To view what the default value is for max_allowed_packet variable, execute the following command in in MySQL:

show variables like 'max_allowed_packet';

Standard MySQL installation has a default value of 1048576 bytes (1MB). This can be increased by setting it to a higher value for a session or connection.

This sets the value to 500MB for everyone (that’s what GLOBAL means):

SET GLOBAL max_allowed_packet=524288000;

Then you can run the show GLOBAL variables command to check that the value has changed.

show GLOBAL variables like 'max_allowed_packet';

Or run this show variables command in a new connection (e.g. re-open your client tool).

show variables like 'max_allowed_packet';

The problem with using command above is that when the MySQL server is restarted, max_allowed_packet is reset to its default value. To permanently change max_allowed_packet, edit your my.ini configuration file under [mysqld] in the SERVER SECTION. Either add the single line under [mysqld] or edit its value if your my.ini has already got this line. Then restart the MySQL service to make this value take effect.

 

As MySQL puts, it is safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you do not run out of memory by using large packets accidentally.