importing bulk data in mysql and mariadb
We can import bulk data from other data files easily with MARIADB or MYSQL by using the LOAD DATA INFILE command.
In order for this command to work we must tell MYSQL how does the data is separated in the file it read from.
Example of the command:
- LOAD DATA INFILE data file location and its name INTO TABLE database name.table name FIELDS TERMINATED BY termination symbol;
Loading only specific column
We can load only specific column from the load file by specify the name of the column at the end of this statement, the name must match the name of the column found in MYSQL table which data is been imported.
- LOAD DATA INFILE tmp/names.csv INTO TABLE records.names FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' " ' IGNORE 1 LINES (id, change types, @niente, @niente, scientific name);
we use the @niente variable to store all values that we don't need to be read into the table an MYSQL will assign values that are corresponding to the name of their columns in the table.
We tell mysql that there are values found inside the double quotes but not all values are found inside the double quotes that why we use OPTIONALLY ENCLOSED BY and the IGNORE 1 LINES, this is used to tell mysql to ignore the value of the first line starting from the beginning of the data file text.
We can also tell mysql about where lines starting and where lines ending and other values by using these option.
- LOAD DATA INFILE location.cs INTO TABLE test_db.table FIELDS TERMINATED BY '|' ENCLOSED BY ' " ' ESCAPED BY '\\' LINES STARTING BY '[' TERMINATED BY '] ' IGNORE 1 LINES (name, password, email, country);
The terminated by tell mysql that fields end with a vertical bar. Enclosed by tells each field is positioned between double quotes.
The escape by this specified the character that's used to escape special characters, the default is a backslash character but we can also specify.
The starting by specified an opening bracket. The terminated by specified a closing bracket followed by a carriage return and a new line.
The one disadvantage of this method of enter data is when error occurred the whole operation fail to insert data, so to prevent this behavior we use the IGNORE clause behind INTO TABLE.