如何用PHP函数导入.sql文件？XAMPP环境下导入失败求助

Hey there! Let's dig into why your SQL import isn't working with your PHP script. The biggest red flag I see right now is how you're referencing the SQL file.

The Core Problem #

The mysql command-line tool can't read files from HTTP URLs (like http://localhost/import_tbl/postcode_withLatlang.sql). It needs a local file path on your server's filesystem instead.

Step-by-Step Fixes #

1. Use a Local File Path
   Replace the HTTP URL with a local relative or absolute path to your SQL file. For example:

   • If your PHP script is in the same import_tbl folder as the SQL file, use a relative path:

     $mysqlImportFilename = './postcode_withLatlang.sql';
     

   • Or use an absolute path (adjust based on your OS):
     • Windows: C:/xampp/htdocs/import_tbl/postcode_withLatlang.sql
     • Linux/Mac: /opt/lampp/htdocs/import_tbl/postcode_withLatlang.sql

2. Clean Up the Command Syntax
   When dealing with an empty password (like your root user), you can omit the -p flag entirely to avoid unnecessary prompts. Here's the corrected command:

   $command = 'mysql -h' . $mysqlHostName . ' -u' . $mysqlUserName . ' ' . $mysqlDatabaseName . ' < ' . $mysqlImportFilename;
   

   If you later add a password, format it as -p' . $mysqlPassword (no space between -p and the password) to avoid issues in the command string.

3. Verify File Permissions
   Make sure the XAMPP process (Apache/PHP) has permission to read the SQL file:

   • On Windows: Right-click the file → Properties → Ensure "Read-only" isn't checked, and the user running XAMPP has access.
   • On Linux/Mac: Run this command in the terminal to set appropriate permissions:

     chmod 644 /opt/lampp/htdocs/import_tbl/postcode_withLatlang.sql
     

4. Test the Command Manually First
   Before relying on PHP, test the import directly in XAMPP's Shell to catch errors:

   1. Open the XAMPP Control Panel.
   2. Click the "Shell" button.
   3. Run your adjusted command (replace the path with your local one):

      mysql -h localhost -u root postcodes < C:/xampp/htdocs/import_tbl/postcode_withLatlang.sql
      

   Any errors here will tell you exactly what's wrong (e.g., missing file, invalid SQL syntax).

5. Capture PHP Command Output for Debugging
   Modify your PHP script to capture the command's output and return code—this will help you diagnose issues if the import still fails:

   $output = [];
   $returnCode = 0;
   exec($command, $output, $returnCode);
   
   // Print results to debug
   echo "<h3>Debug Info:</h3>";
   echo "<p>Return Code: $returnCode</p>";
   echo "<pre>Output:\n" . print_r($output, true) . "</pre>";
   

Final Notes #

If you're still stuck, check if the SQL file itself has syntax errors—open it in a text editor or use phpMyAdmin to try importing it directly. phpMyAdmin will give you detailed error messages if something's wrong with the SQL structure.

内容的提问来源于stack exchange，提问作者surma