如何使用PHP将.sql文件导入到MySQL数据库中？

另一种简单的方法是复制Adminer，然后导入SQL。

通过 PHP 代码帮助导入 SQL 文件数据

// sql file path
$filename = 'database_file_name.sql';

// Add this function into your helper file and add any where that you want to use
function import_tables($host,$uname,$pass,$database, $filename,$tables = '*'){
    $connection = mysqli_connect($host,$uname,$pass) or die("Database Connection Failed");
    $selectdb = mysqli_select_db($connection, $database) or die("Database could not be selected"); 
    
    $templine = '';
    $lines = file($filename); // Read entire file

    foreach ($lines as $line){
        // Skip it if it's a comment
        if (substr($line, 0, 2) == '--' || $line == '' || substr($line, 0, 2) == '/*' )
            continue;

            // Add this line to the current segment
            $templine .= $line;
            // If it has a semicolon at the end, it's the end of the query
            if (substr(trim($line), -1, 1) == ';')
            {
                mysqli_query($connection, $templine)
                or print('Error performing query \'<strong>' . $templine . '\': ' . mysqli_error($connection) . '<br /><br />');
                $templine = '';
            }
        }
        echo "Tables imported successfully";
    }

// Calling import menthod
backup_tables('database_hostname','database_username','database_password','database_name');
// backup_tables function have 4paramater - database hostname, username, password and database name that you want import sql data.

如果你想从 PHP 脚本备份/导出数据库

// Call the backup_tables for export the database
backup_tables('hostname','UserName','pass','databses_name');
    
function backup_tables($host,$user,$pass,$name,$tables = '*'){
        $link = mysqli_connect($host,$user,$pass);
        if (mysqli_connect_errno()){
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }
        
        mysqli_select_db($link,$name);
        //get all of the tables
        if($tables == '*'){
            $tables = array();
            $result = mysqli_query($link,'SHOW TABLES');
            while($row = mysqli_fetch_row($result))
            {
                $tables[] = $row[0];
            }
        }else{
            $tables = is_array($tables) ? $tables : explode(',',$tables);
        }

        $return = '';
        foreach($tables as $table)
        {
            $result = mysqli_query($link,'SELECT * FROM '.$table);
            $num_fields = mysqli_num_fields($result);
            $row_query = mysqli_query($link,'SHOW CREATE TABLE '.$table);
            $row2 = mysqli_fetch_row($row_query);
            $return.= "\n\n".$row2[1].";\n\n";

            for ($i = 0; $i < $num_fields; $i++) 
            {
                while($row = mysqli_fetch_row($result))
                {
                    $return.= 'INSERT INTO '.$table.' VALUES(';
                    for($j=0; $j < $num_fields; $j++) 
                    {
                        $row[$j] = addslashes($row[$j]);
                        $row[$j] = str_replace("\n", '\n', $row[$j]);
                        if (isset($row[$j])) { 
                            $return.= '"'.$row[$j].'"' ; 
                        } else { 
                            $return.= '""'; 
                        }
                        if ($j < ($num_fields-1)) { $return.= ','; }
                    }
                    $return.= ");\n";
                }
            }
            $return.="\n\n\n";
        }

        //save file
        $handle = fopen('backup-'.date("d_m_Y__h_i_s_A").'-'.(md5(implode(',',$tables))).'.sql','w+');
        fwrite($handle,$return);
        fclose($handle);
    }

function restoreDatabase($db_name,$file_path)
{

    //checking valid extension file
    $path_parts = pathinfo($file_path);
    $ext_file = $path_parts['extension'];
    $filename = $path_parts['basename'];

    if($ext_file == "sql")
    {
         $c = new Config();

         $confJson = $c->getConfig();
         $conf = json_decode($confJson);

         $dbhost   = "127.0.0.1";
         $dbuser   = $conf->db_username;  
         $dbpwd    = $conf->db_password;
         $dbname   = $db_name;   

         $dumpfile = $file_path;

         $is_file = file_exists($file_path);
         if($is_file == TRUE)
         {

             //passthru("/usr/bin/mysqldump --opt --host=$dbhost --user=$dbuser --password=$dbpwd $dbname < $dumpfile");

             //passthru("tail -1 $dumpfile");
             system('mysql --user='.$dbuser.' --password='.$dbpwd.' '.$db_name.' < '.$file_path);
             return "Database was restored from $filename ";

         }
         else 
         {
             return "Restore database was aborted due ".$filename." does not exist!";
         }


    }
    else
    {
        return "Invalid file format.Require sql file to restore this ".$db_name." database. ".$filename." is not sql file format\n(eg. mybackupfile.sql).";
    }
}

<?php
// Name of the file
$filename = 'db.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'root';
// MySQL password
$mysql_password = '123456';
// Database name
$mysql_database = 'mydb';

$connection = mysqli_connect($mysql_host,$mysql_username,$mysql_password,$mysql_database) or die(mysqli_error($connection));

// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line)
{
// Skip it if it's a comment
if (substr($line, 0, 2) == '--' || $line == '')
    continue;

// Add this line to the current segment
$templine .= $line;
// If it has a semicolon at the end, it's the end of the query
if (substr(trim($line), -1, 1) == ';')
{
    // Perform the query
    mysqli_query($connection,$templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysqli_error($connection) . '<br /><br />');
    // Reset temp variable to empty
    $templine = '';
}
}
 echo "Tables imported successfully";
?>

我觉得你可以尝试这段代码，它对我的情况有效：

<?php

$con = mysqli_connect('localhost', 'root', 'NOTSHOWN', 'test');

$filename = 'dbbackupmember.sql';
$handle = fopen($filename, 'r+');
$contents = fread($handle, filesize($filename));

$sql = explode(";", $contents);
foreach ($sql as $query) {
 $result = mysqli_query($con, $query);
 if ($result) {
  echo "<tr><td><br></td></tr>";
  echo "<tr><td>".$query."</td></tr>";
  echo "<tr><td><br></td></tr>";
 }
}

fclose($handle);
echo "success";


?>

我使用这段代码并成功运行：

$filename = 'apptoko-2016-12-23.sql'; //change to ur .sql file
                            $handle = fopen($filename, "r+");
                            $contents = fread($handle, filesize($filename));

                            $sql = explode(";",$contents);// 
                            foreach($sql as $query){
                                $result=mysql_query($query);
                                if ($result){
                                 echo '<tr><td><BR></td></tr>';
                                 echo '<tr><td>' . $query . ' <b>SUCCESS</b></td></tr>';
                                 echo '<tr><td><BR></td></tr>';
                                }
                            }
                            fclose($handle);

解决特殊字符问题

 $link=mysql_connect($dbHost, $dbUser, $dbPass) OR die('connecting to host: '.$dbHost.' failed: '.mysql_error());
mysql_select_db($dbName) OR die('select db: '.$dbName.' failed: '.mysql_error());

//charset important
mysql_set_charset('utf8',$link);