Today I discovered a clever feature that MySQL has, namely writing data to a table from a file.

If you have or have had need to be able to write data using batch insert, ie. write several lines of data using a sql insert then you might have discovered that ordinary insert has limitations in number of parameters and that it takes some time to execute.

Test Table

Let me show you the difference between "normal" batch insert and MySQL LOAD DATA INFILE. We start by creating a test table that we can fill with data.

CREATE TABLE test_data
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, test_field TEXT NOT NULL);


Usual batch writing, small amounts of data

The usual batch writing is simple but has limitations in the number of parameters that can be printed in a single insert. When you have a small amounts of data then batch-writing, might do the trick. It looks something like this

INSERT INTO test_data (test_field), VALUES ('a text"),("a text too"),("more text")


Large amounts of data

If you, like us at work, have large amounts of data that must be written to the database at the same time, you should instead look at the MySQL LOAD DATA INFILE.

What needs to be done when you want to use load data infile is to create a file with data in which each row represents a row in the table you want to write data.

data.txt

"\N", "a text åäö"
"\N", "a text åäö, with comma"
"\N", "a text åäö with \" quotes"
"\N", "a text with \", quote and comma"


"\N" I write to auto_increment to work. You can write "NULL" instead of "\N".

Here is the SQL query that you run to write the data in the file 'data.txt' into table test_data.

LOAD DATA INFILE 'absolute/path/to/data.txt' INTO TABLE test_data
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';


You will probably not notice any difference in speed if you just write a few lines to the database, however, the difference becomes significant when you are approaching 4-5000 rows.

LOAD DATA INFILE, problems with UTF-8?

If you have problems with UTF-8 when running database queries, it may be that the php file, data.txt, database, table or column in the database table is not in UTF-8.

If you want to fix the problem without bothering having to fix the UTF-8 problems you can use the function that I found at semper solutions.

function fixEncoding($input) (
$cur_encoding = mb_detect_encoding ($ input);
if ($cur_encoding == 'UTF-8' && mb_check_encoding ($input, "UTF-8"))
return $input;
else
Return utf8_encode($input);
)


Good luck with the code!