Read large files to MySQL

Originally used in Java read file processing format after the database, run, found that the efficiency is too low, 4G text, more than 40 million lines, estimated to take about 30 days to complete processing.

Data format:

Each line, XXXX | | | XXXXXXX | | | Xxxxxxxxxxxxxxxxx

Data is strictly structured, so query to a method of importing MySQL directly from a file:

Load Data infile

Reference article: http://hunan.iteye.com/blog/752606

The following directives are used:

according to [| | |] Split a row of data into three segments, then each line is identified by \ n, and three pieces of data are stored in three fields respectively.

Successfully imported 47943428 rows!

After importing, we find that there is a \ R presence at the end of each line, removing the \ r symbol

Update Detail_all Set value = Trim (TRAILING ' \ R ' from ' value ');

Remove the opening and final symbols with trim. Reference: http://nameyjj.blog.51cto.com/788669/621932

Run Times Error:

Error code:2013. Lost connection to MySQL server during query600.746 sec

Find a solution: http://www.quora.com/ How-can-i-solve-the-error-code-2013-lost-connection-to-mysql-server-during-query-600-135-sec-error-message

That is, the time-out will be adjusted to large.

Then run the error:

Error code:1205. Lock wait timeout exceeded; Try restarting transaction

Find a solution: http://blog.csdn.net/mchdba/article/details/38313881

I am here to roll back the process is not over, wait for it to end.

Read large files to MySQL