How to Import Large MySQL Files in PHP: A Memory-Efficient Solution with Split-Query Approach?

Importing a Large MySQL File in PHP: A Comprehensive Split-Query Solution

Importing a large MySQL file into a shared hosting provider website can be a challenge, especially when using PHP and external access to MySQL is restricted. This article provides a comprehensive solution that addresses this issue by reliably splitting the file into individual queries for import.

Challenges to Consider

When parsing and querying a large MySQL file in PHP, several challenges arise:

• Detecting field delimiters within data
• Handling line breaks in memo fields
• Avoiding memory issues due to large file size

A Reliable Split-Query Solution

To overcome these challenges, this article introduces a memory-friendly function called SplitSQL(). This function reads the file line-by-line and accumulates individual queries until a query delimiter (such as a semicolon) is encountered. Here is the code:

function SplitSQL($file, $delimiter = ';') {
  set_time_limit(0);
  if (is_file($file) && is_resource($file = fopen($file, 'r'))) {
    $query = array();
    while (!feof($file)) {
      $query[] = fgets($file);
      if (preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query))) {
        $query = trim(implode('', $query));
        if (mysql_query($query) === false) {
          echo 'ERROR: ' . $query . "\n";
        } else {
          echo 'SUCCESS: ' . $query . "\n";
          flush();
        }
        $query = array();
      }
    }
    fclose($file);
    return true;
  }
  return false;
}

Memory-Friendly and Tested

This function allocates memory only as needed, avoiding memory issues. It has been tested on large phpMyAdmin SQL dumps, demonstrating its ability to handle real-world data effectively.

Test Data and Output

Sample data and its respective output using the SplitSQL() function:

Test Data:

CREATE TABLE IF NOT EXISTS "test" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT,
  "description" TEXT
);

BEGIN;
INSERT INTO "test" ("name", "description")
VALUES (";;;", "something for you mind; body; soul");
COMMIT;

UPDATE "test"
SET "name" = "; "
WHERE "id" = 1;

Output:

SUCCESS: CREATE TABLE IF NOT EXISTS "test" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "description" TEXT );
SUCCESS: BEGIN;
SUCCESS: INSERT INTO "test" ("name", "description") VALUES (";;;", "something for you mind; body; soul");
SUCCESS: COMMIT;
SUCCESS: UPDATE "test" SET "name" = "; " WHERE "id" = 1;

Conclusion

The SplitSQL() function provides a robust and reliable solution for splitting large MySQL files into individual queries, allowing efficient and automated database import in shared hosting environments. By addressing memory constraints and accounting for various data types, this function simplifies the complex process of importing large MySQL files in PHP.

The above is the detailed content of How to Import Large MySQL Files in PHP: A Memory-Efficient Solution with Split-Query Approach?. For more information, please follow other related articles on the PHP Chinese website!