Loading data file into MySql Database
Loading data into mysql tables can be done using simple insert statements but to get achieve better speed and performance it requires a special tool and LOAD DATA INFILE command is provided for this purpose in MySql. Used it recently and worked really fast, remember to drop indexes before this and rebuild them in case you have bigger table to restore.
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
This command is complement of SELECT … INTO OUTFILE command which can produce delimited file directly from the SELECT query and LOAD DATA INFILE can use the same file using very similar command.
LOAD DATA INFILE provides many advantages over normal INSERT statement which can be optimized upto some extent but not much. You can visit following link for getting more information about speed of INSERT statement and what optimizations can be done.
In case of LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table and If you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing.
If you want to ignore foreign key constraints during the load operation, you can issue a SET foreign_key_checks = 0 statement before executing LOAD DATA.
LOAD DATA INFILE 'data.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' INTO TABLE tbl_name (col1, col2, col3...);
For more details refer here
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment