What are the advantages and disadvantages of using LOAD DATA INFILE compared to individual MySQL INSERT queries when importing CSV data into a database with PHP?

When importing CSV data into a MySQL database with PHP, using LOAD DATA INFILE is generally faster and more efficient than individual INSERT queries. However, using LOAD DATA INFILE requires additional permissions and may not be suitable for all situations, such as when data needs to be manipulated before insertion. 

// Using LOAD DATA INFILE to import CSV data into MySQL database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "LOAD DATA INFILE 'path/to/file.csv' 
        INTO TABLE table_name 
        FIELDS TERMINATED BY ',' 
        LINES TERMINATED BY '\n' 
        IGNORE 1 LINES";

if ($conn->query($sql) === TRUE) {
    echo "CSV data imported successfully";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();

Keywords

LOAD DATA INFILE MySQL INSERT queries CSV data database PHP

Related Questions