如何使用 PHP 在 mysql 数据库中导入 .sql 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19751354/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I import a .sql file in mysql database using PHP?
提问by msalman
I'm trying to import a .sql file through PHP code. However, my code shows this error:
我正在尝试通过 PHP 代码导入 .sql 文件。但是，我的代码显示此错误：
There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:
MySQL Database Name: test
MySQL User Name: root
MySQL Password: NOTSHOWN
MySQL Host Name: localhost
MySQL Import Filename: dbbackupmember.sql
And this is my code:
这是我的代码：
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='test';
$mysqlUserName ='root';
$mysqlPassword ='';
$mysqlHostName ='localhost';
$mysqlImportFilename ='dbbackupmember.sql';
//DONT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
exec($command,$output=array(),$worked);
switch($worked){
case 0:
echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
break;
case 1:
echo 'There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:<br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr><tr><td>MySQL Import Filename:</td><td><b>' .$mysqlImportFilename .'</b></td></tr></table>';
break;
}
?>
What am I doing wrong? The SQL file is in the same directory.
我究竟做错了什么？SQL 文件位于同一目录中。
回答by ManojGeek
Warning:
mysql_*extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqlior PDO_MySQLextension should be used. See also the MySQL API Overviewfor further help while choosing a MySQL API.
Whenever possible, importing a file to MySQL should be delegated to MySQL client.
警告：
mysql_*扩展自 PHP 5.5.0 起已弃用，自 PHP 7.0.0 起已被删除。相反，应该使用mysqli或PDO_MySQL扩展。在选择 MySQL API 时，另请参阅MySQL API 概述以获取进一步帮助。
只要有可能，将文件导入 MySQL 应委托给 MySQL 客户端。
I have got another way to do this, try this
我有另一种方法可以做到这一点，试试这个
<?php
// Name of the file
$filename = 'churc.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'root';
// MySQL password
$mysql_password = '';
// Database name
$mysql_database = 'dump';
// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());
// 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
mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
// Reset temp variable to empty
$templine = '';
}
}
echo "Tables imported successfully";
?>
This is working for me
这对我有用
回答by Grain
Warning:
mysql_*extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqlior PDO_MySQLextension should be used. See also the MySQL API Overviewfor further help while choosing a MySQL API.
Whenever possible, importing a file to MySQL should be delegated to MySQL client.
警告：
mysql_*扩展自 PHP 5.5.0 起已弃用，自 PHP 7.0.0 起已被删除。相反，应该使用mysqli或PDO_MySQL扩展。在选择 MySQL API 时，另请参阅MySQL API 概述以获取进一步帮助。
只要有可能，将文件导入 MySQL 应委托给 MySQL 客户端。
the answer from Raj is useful, but (because of file($filename)) it will fail if your mysql-dump not fits in memory
Raj 的答案很有用，但是（因为 file($filename)）如果您的 mysql-dump 不适合内存，它将失败
If you are on shared hostingand there are limitationslike 30 MB and 12s Script runtime and you have to restore a x00MB mysql dump, you can use this script:
如果您在共享主机上并且存在30 MB 和 12s 脚本运行时等限制，并且您必须恢复 x00MB mysql 转储，则可以使用此脚本：
it will walk the dumpfile query for query, if the script execution deadline is near, it saves the current fileposition in a tmp file and a automatic browser reload will continue this process again and again ... If an error occurs, the reload will stop and an the error is shown ...
它将遍历转储文件查询以进行查询，如果脚本执行截止日期临近，它将当前文件位置保存在 tmp 文件中，浏览器自动重新加载将一次又一次地继续此过程......如果发生错误，重新加载将停止并显示错误...
if you comeback from lunch your db will be restored ;-)
如果你从午餐回来，你的数据库将被恢复;-)
the noLimitDumpRestore.php:
noLimitDumpRestore.php：
// your config
$filename = 'yourGigaByteDump.sql';
$dbHost = 'localhost';
$dbUser = 'user';
$dbPass = '__pass__';
$dbName = 'dbname';
$maxRuntime = 8; // less then your max script execution limit
$deadline = time()+$maxRuntime;
$progressFilename = $filename.'_filepointer'; // tmp file for progress
$errorFilename = $filename.'_error'; // tmp file for erro
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());
($fp = fopen($filename, 'r')) OR die('failed to open file:'.$filename);
// check for previous error
if( file_exists($errorFilename) ){
die('<pre> previous error: '.file_get_contents($errorFilename));
}
// activate automatic reload in browser
echo '<html><head> <meta http-equiv="refresh" content="'.($maxRuntime+2).'"><pre>';
// go to previous file position
$filePosition = 0;
if( file_exists($progressFilename) ){
$filePosition = file_get_contents($progressFilename);
fseek($fp, $filePosition);
}
$queryCount = 0;
$query = '';
while( $deadline>time() AND ($line=fgets($fp, 1024000)) ){
if(substr($line,0,2)=='--' OR trim($line)=='' ){
continue;
}
$query .= $line;
if( substr(trim($query),-1)==';' ){
if( !mysql_query($query) ){
$error = 'Error performing query \'<strong>' . $query . '\': ' . mysql_error();
file_put_contents($errorFilename, $error."\n");
exit;
}
$query = '';
file_put_contents($progressFilename, ftell($fp)); // save the current file position for
$queryCount++;
}
}
if( feof($fp) ){
echo 'dump successfully restored!';
}else{
echo ftell($fp).'/'.filesize($filename).' '.(round(ftell($fp)/filesize($filename), 2)*100).'%'."\n";
echo $queryCount.' queries processed! please reload or wait for automatic browser refresh!';
}
回答by RahulD
You can use the mysqli multi_query function as below:
您可以使用 mysqli multi_query 函数，如下所示：
$sql = file_get_contents('mysqldump.sql');
$mysqli = new mysqli("localhost", "root", "pass", "testdb");
/* execute multi query */
$mysqli->multi_query($sql);
回答by Patel Yatin
<?php
$host = "localhost";
$uname = "root";
$pass = "";
$database = "demo1"; //Change Your Database Name
$conn = new mysqli($host, $uname, $pass, $database);
$filename = 'users.sql'; //How to Create SQL File Step : url:http://localhost/phpmyadmin->detabase select->table select->Export(In Upper Toolbar)->Go:DOWNLOAD .SQL FILE
$op_data = '';
$lines = file($filename);
foreach ($lines as $line)
{
if (substr($line, 0, 2) == '--' || $line == '')//This IF Remove Comment Inside SQL FILE
{
continue;
}
$op_data .= $line;
if (substr(trim($line), -1, 1) == ';')//Breack Line Upto ';' NEW QUERY
{
$conn->query($op_data);
$op_data = '';
}
}
echo "Table Created Inside " . $database . " Database.......";
?>
回答by Leonardo Filipe
<?php
system('mysql --user=USER --password=PASSWORD DATABASE< FOLDER/.sql');
?>
回答by Skylex
I have Test your code, this error shows when you already have the DB imported or with some tables with the same name, also the Array error that shows is because you add in in the exec parenthesis, here is the fixed version:
我有测试你的代码，当你已经导入了数据库或一些同名的表时，这个错误显示，显示的数组错误也是因为你在 exec 括号中添加，这里是固定版本：
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='test';
$mysqlUserName ='root';
$mysqlPassword ='';
$mysqlHostName ='localhost';
$mysqlImportFilename ='dbbackupmember.sql';
//DONT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
$output=array();
exec($command,$output,$worked);
switch($worked){
case 0:
echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
break;
case 1:
echo 'There was an error during import.';
break;
}
?>
回答by Igweze Ebele Mark
Grain Script is superb and save my day. Meanwhile mysql is depreciated and I rewrote Grain answer using PDO.
Grain Script 非常棒，拯救了我的一天。同时 mysql 已贬值，我使用 PDO 重写了 Grain 答案。
$server = 'localhost';
$username = 'root';
$password = 'your password';
$database = 'sample_db';
/* PDO connection start */
$conn = new PDO("mysql:host=$server; dbname=$database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("SET CHARACTER SET utf8");
/* PDO connection end */
// your config
$filename = 'yourFile.sql';
$maxRuntime = 8; // less then your max script execution limit
$deadline = time()+$maxRuntime;
$progressFilename = $filename.'_filepointer'; // tmp file for progress
$errorFilename = $filename.'_error'; // tmp file for erro
($fp = fopen($filename, 'r')) OR die('failed to open file:'.$filename);
// check for previous error
if( file_exists($errorFilename) ){
die('<pre> previous error: '.file_get_contents($errorFilename));
}
// activate automatic reload in browser
echo '<html><head> <meta http-equiv="refresh" content="'.($maxRuntime+2).'"><pre>';
// go to previous file position
$filePosition = 0;
if( file_exists($progressFilename) ){
$filePosition = file_get_contents($progressFilename);
fseek($fp, $filePosition);
}
$queryCount = 0;
$query = '';
while( $deadline>time() AND ($line=fgets($fp, 1024000)) ){
if(substr($line,0,2)=='--' OR trim($line)=='' ){
continue;
}
$query .= $line;
if( substr(trim($query),-1)==';' ){
$igweze_prep= $conn->prepare($query);
if(!($igweze_prep->execute())){
$error = 'Error performing query \'<strong>' . $query . '\': ' . print_r($conn->errorInfo());
file_put_contents($errorFilename, $error."\n");
exit;
}
$query = '';
file_put_contents($progressFilename, ftell($fp)); // save the current file position for
$queryCount++;
}
}
if( feof($fp) ){
echo 'dump successfully restored!';
}else{
echo ftell($fp).'/'.filesize($filename).' '.(round(ftell($fp)/filesize($filename), 2)*100).'%'."\n";
echo $queryCount.' queries processed! please reload or wait for automatic browser refresh!';
}
回答by JJ Labajo
If you need a User Interface and if you want to use PDO
如果您需要用户界面并且您想使用 PDO
Here's a simple solution
这是一个简单的解决方案
<form method="post" enctype="multipart/form-data">
<input type="text" name="db" placeholder="Databasename" />
<input type="file" name="file">
<input type="submit" name="submit" value="submit">
</form>
<?php
if(isset($_POST['submit'])){
$query = file_get_contents($_FILES["file"]["name"]);
$dbname = $_POST['db'];
$con = new PDO("mysql:host=localhost;dbname=$dbname","root","");
$stmt = $con->prepare($query);
if($stmt->execute()){
echo "Successfully imported to the $dbname.";
}
}
?>
Definitely working on my end. Worth a try.
绝对是我的目的。值得一试。
回答by Ram Kumawat
// Import data
$filename = 'database_file_name.sql';
import_tables('localhost','root','','database_name',$filename);
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";
}
// Backup database from php script
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);
}
回答by Kazim Noorani
If you are using PHP version 7 or higher, try below script,
如果您使用的是 PHP 7 或更高版本，请尝试以下脚本，
// Name of the file
$filename = 'sql.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'username';
// MySQL password
$mysql_password = 'password';
// Database name
$mysql_database = 'database';
// Connect to MySQL server
$con = @new mysqli($mysql_host,$mysql_username,$mysql_password,$mysql_database);
// Check connection
if ($con->connect_errno) {
echo "Failed to connect to MySQL: " . $con->connect_errno;
echo "<br/>Error: " . $con->connect_error;
}
// 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
$con->query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . $con->error() . '<br /><br />');
// Reset temp variable to empty
$templine = '';
}
}
echo "Tables imported successfully";
$con->close($con);
