split_and_load.sh

#!/bin/bash

function loadFile(){
        mysql -u"[USERNAME]" -p"[USERNAME]" --local-infile=1 [DATABASE_NAME] << EOF
        SET GLOBAL local_infile = 'ON'; 
                LOAD DATA LOCAL INFILE "$1" INTO TABLE $2
                        COLUMNS TERMINATED BY ','
                        OPTIONALLY ENCLOSED BY '"'
                        ESCAPED BY '"'
                        LINES TERMINATED BY '\n'
                        IGNORE 1 LINES;
exit
EOF
}

echo "data is: $1"
echo "table is $2"

mkdir $1
mv $1.csv $1/
cd $1/
echo "spliting file..."
split -l 10000 $1.csv -d data
rename 's/$/\.csv/'  *
echo "the split done"
echo "tranfer the csv file into database [DATABASE_NAME] table $2"
sleep 3

for fileName in data*; do
        echo "begin load file $fileName..."
        loadFile $fileName $2;
        rm -rf $fileName;
        echo "finsh load file $fileName and deleted"
done

cd ..
rm -rf $1/

When we facing a large .csv or .txt file, we can:

1. split the file by using the built-in function split, l means how mean lines in one separated file and -d means the prefix;
2. then load a bash function to do the for loop and load all the "tiny" file into the database

If you are facing a .txt file, the default .sql may not suitable for you as it separates the line by a comma, which may suitable for most of the .csv file.