What are the advantages and disadvantages of using the LOAD DATA INFILE statement in MySQL for bulk data insertion compared to individual INSERT statements in PHP?

Using the LOAD DATA INFILE statement in MySQL for bulk data insertion is more efficient than using individual INSERT statements in PHP because it reduces the number of round trips to the database, resulting in faster data insertion. However, using LOAD DATA INFILE may require additional permissions and may not be suitable for all scenarios, such as when data needs to be manipulated before insertion. 

// Example of using LOAD DATA INFILE for bulk data insertion in MySQL
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// Load data from file into table
$sql = "LOAD DATA INFILE 'data.csv' INTO TABLE myTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";
if ($conn->query($sql) === TRUE) {
    echo "Data inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

// Close connection
$conn->close();

Keywords

LOAD DATA INFILE MySQL bulk data insertion INSERT statements PHP

Related Questions