Doyenhub

logo
logo
logo
logo

OUR BLOGS

May 23, 2017

Bulk Insert in MySql using LOAD DATA INFILE

Looking for a Fast Way to Insert Bulk Data into MySQL?

If you’re searching for an efficient way to manage bulk insert operations, Bulk Insert in MySQL using LOAD DATA INFILE is the perfect solution. This MySQL feature allows quick and seamless bulk data insertion.

In this blog, we’ll explore how to use LOAD DATA INFILE for bulk insert functionality in PHP applications. A guide on importing bulk records directly into MySQL will follow in a future post.

Implementing Bulk Insert in PHP Applications

Whether your application handles bulk insert operations via a user interface or a cron job, LOAD DATA INFILE is ideal for optimizing data processing.

Use Case Example:
In a recent project, I needed to sync records from a local database to a global database. The challenge? The client required this functionality via a button click, with no cron job involved. Waiting over 5 seconds for bulk insertion was unacceptable.

After researching, I implemented the LOAD DATA INFILE concept, which dramatically reduced processing time.

How to Use LOAD DATA INFILE in PHP

Step 1: Prepare Your Data
Ensure your data is in CSV format (comma-separated values).

Step 2: Create the PHP Script

Below is a PHP script for inserting bulk data using LOAD DATA INFILE:

php

$host = “localhost”;
$uname = “root”;
$pass = “”;
$database = “dbname”;

$table1 = trim($_GET[‘tablename’]);
$fileName_csv = trim($_GET[‘filename’]);

$connection = mysqli_connect($host, $uname, $pass, $database) or die(“Database Connection Failed”);

if (mysqli_connect_errno()) {
echo “Failed to connect to MySQL: ” . mysqli_connect_error();
exit();
}

$filename = “C:/path_to_file/” . $fileName_csv;
$query = “LOAD DATA INFILE ‘$filename’ INTO TABLE $table1 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘”;

if (!mysqli_query($connection, $query)) {
echo “Error: ” . mysqli_error($connection);
} else {
echo “Bulk Insert Successful!”;
}

mysqli_close($connection);

Step 3: Execute the Script

Upload your CSV file to the specified path and run the script.

Best Practices for Using LOAD DATA INFILE

  1. File Format: Ensure the file matches the table structure.
  2. Error Handling: Use try-catch blocks to manage errors.
  3. Performance: Use indexes and optimize your table structure for better results.

Why Choose LOAD DATA INFILE?

This method is faster than traditional INSERT queries, making it ideal for large datasets. By leveraging CSV files, you simplify data handling and improve efficiency.

Have questions or need guidance? Let me know in the comments!

Also Read –N ideas to increase performance of website – Part 1

Leave a comment

Your email address will not be published. Required fields are marked *