从PHP中加载.sql文件

I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned to open the file and mysql_query it a line at a time - until I looked at the schema files and realised they aren't just one query per line.

我正在为正在开发的应用程序创建一个安装脚本，需要在PHP中动态创建数据库。我已经创建了数据库，但是现在需要加载几个.sql文件。我曾计划一次打开这个文件和mysql_query，直到我查看模式文件并意识到它们不是每行一个查询。

So, how do I load an sql file from within PHP (as phpMyAdmin does with its import command)?

那么，如何从PHP中加载sql文件(就像phpMyAdmin使用它的import命令那样)?

29 个解决方案

#1

I'm getting the feeling that everyone here who's answered this question doesn't know what it's like to be a web application developer who allows people to install the application on their own servers. Shared hosting, especially, doesn't allow you to use SQL like the "LOAD DATA" query mentioned previously. Most shared hosts also don't allow you to use shell_exec.

我感觉这里回答这个问题的每个人都不知道web应用程序开发人员允许人们在自己的服务器上安装应用程序是什么感觉。共享主机，特别是，不允许您使用像前面提到的“加载数据”查询那样的SQL。大多数共享主机也不允许您使用shell_exec。

Now, to answer the OP, your best bet is to just build out a PHP file that contains your queries in a variable and can just run them. If you're determined to parse .sql files, you should look into phpMyAdmin and get some ideas for getting data out of .sql files that way. Look around at other web applications that have installers and you'll see that, rather than use .sql files for their queries, they just package them up in PHP files and just run each string through mysql_query or whatever it is that they need to do.

现在，要回答这个OP，最好的办法是构建一个PHP文件，其中包含一个变量中的查询，并可以运行它们。如果您决定解析.sql文件，那么应该查看phpMyAdmin，并通过这种方式从.sql文件中获取数据。看看其他有安装程序的web应用程序，你会发现，它们不会在查询中使用.sql文件，而是将它们打包到PHP文件中，然后通过mysql_query或任何它们需要执行的操作来运行每个字符串。

#2

$db = new PDO($dsn, $user, $password);

$sql = file_get_contents('file.sql');

$qr = $db->exec($sql);

#3

phpBB uses a few functions to parse their files. They are rather well-commented (what an exception!) so you can easily know what they do (I got this solution from http://www.frihost.com/forums/vt-8194.html). here is the solution an I've used it a lot:

phpBB使用一些函数来解析它们的文件。他们是相当好的注释(一个例外!)，所以你可以很容易地知道他们做了什么(我从http://www.frihost.com/forums/vt-8194.html得到了这个解决方案)。这是我经常使用的解决方案:

<php
ini_set('memory_limit', '5120M');
set_time_limit ( 0 );
/***************************************************************************
*                             sql_parse.php
*                              -------------------
*     begin                : Thu May 31, 2001
*     copyright            : (C) 2001 The phpBB Group
*     email                : [e-mail]
*
*     $Id: sql_parse.php,v 1.8 2002/03/18 23:53:12 psotfx Exp $
*
****************************************************************************/

/***************************************************************************
 *
 *   This program is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 ***************************************************************************/

/***************************************************************************
*
*   These functions are mainly for use in the db_utilities under the admin
*   however in order to make these functions available elsewhere, specifically
*   in the installation phase of phpBB I have seperated out a couple of
*   functions into this file.  JLH
*
\***************************************************************************/

//
// remove_comments will strip the sql comment lines out of an uploaded sql file
// specifically for mssql and postgres type files in the install....
//
function remove_comments(&$output)
{
   $lines = explode("\n", $output);
   $output = "";

   // try to keep mem. use down
   $linecount = count($lines);

   $in_comment = false;
   for($i = 0; $i &lt; $linecount; $i++)
   {
      if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
      {
         $in_comment = true;
      }

      if( !$in_comment )
      {
         $output .= $lines[$i] . "\n";
      }

      if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
      {
         $in_comment = false;
      }
   }

   unset($lines);
   return $output;
}

//
// remove_remarks will strip the sql comment lines out of an uploaded sql file
//
function remove_remarks($sql)
{
   $lines = explode("\n", $sql);

   // try to keep mem. use down
   $sql = "";

   $linecount = count($lines);
   $output = "";

   for ($i = 0; $i &lt; $linecount; $i++)
   {
      if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
      {
         if (isset($lines[$i][0]) && $lines[$i][0] != "#")
         {
            $output .= $lines[$i] . "\n";
         }
         else
         {
            $output .= "\n";
         }
         // Trading a bit of speed for lower mem. use here.
         $lines[$i] = "";
      }
   }

   return $output;

}

//
// split_sql_file will split an uploaded sql file into single sql statements.
// Note: expects trim() to have already been run on $sql.
//
function split_sql_file($sql, $delimiter)
{
   // Split up our string into "possible" SQL statements.
   $tokens = explode($delimiter, $sql);

   // try to save mem.
   $sql = "";
   $output = array();

   // we don't actually care about the matches preg gives us.
   $matches = array();

   // this is faster than calling count($oktens) every time thru the loop.
   $token_count = count($tokens);
   for ($i = 0; $i &lt; $token_count; $i++)
   {
      // Don't wanna add an empty string as the last thing in the array.
      if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
      {
         // This is the total number of single quotes in the token.
         $total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
         // Counts single quotes that are preceded by an odd number of backslashes,
         // which means they're escaped quotes.
         $escaped_quotes = preg_match_all("/(?&lt;!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);

         $unescaped_quotes = $total_quotes - $escaped_quotes;

         // If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
         if (($unescaped_quotes % 2) == 0)
         {
            // It's a complete sql statement.
            $output[] = $tokens[$i];
            // save memory.
            $tokens[$i] = "";
         }
         else
         {
            // incomplete sql statement. keep adding tokens until we have a complete one.
            // $temp will hold what we have so far.
            $temp = $tokens[$i] . $delimiter;
            // save memory..
            $tokens[$i] = "";

            // Do we have a complete statement yet?
            $complete_stmt = false;

            for ($j = $i + 1; (!$complete_stmt && ($j &lt; $token_count)); $j++)
            {
               // This is the total number of single quotes in the token.
               $total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
               // Counts single quotes that are preceded by an odd number of backslashes,
               // which means they're escaped quotes.
               $escaped_quotes = preg_match_all("/(?&lt;!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);

               $unescaped_quotes = $total_quotes - $escaped_quotes;

               if (($unescaped_quotes % 2) == 1)
               {
                  // odd number of unescaped quotes. In combination with the previous incomplete
                  // statement(s), we now have a complete statement. (2 odds always make an even)
                  $output[] = $temp . $tokens[$j];

                  // save memory.
                  $tokens[$j] = "";
                  $temp = "";

                  // exit the loop.
                  $complete_stmt = true;
                  // make sure the outer loop continues at the right point.
                  $i = $j;
               }
               else
               {
                  // even number of unescaped quotes. We still don't have a complete statement.
                  // (1 odd and 1 even always make an odd)
                  $temp .= $tokens[$j] . $delimiter;
                  // save memory.
                  $tokens[$j] = "";
               }

            } // for..
         } // else
      }
   }

   return $output;
}

$dbms_schema = 'yourfile.sql';

$sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema)) or die('problem ');
$sql_query = remove_remarks($sql_query);
$sql_query = split_sql_file($sql_query, ';');

$host = 'localhost';
$user = 'user';
$pass = 'pass';
$db = 'database_name';

//In case mysql is deprecated use mysqli functions. 
mysql_connect($host,$user,$pass) or die('error connection');
mysql_select_db($db) or die('error database selection');

$i=1;
foreach($sql_query as $sql){
echo $i++;
echo "<br />";
mysql_query($sql) or die('error in query');
}

?>

#4

The simplest solution is to use shell_exec() to run the mysql client with the SQL script as input. This might run a little slower because it has to fork, but you can write the code in a couple of minutes and then get back to working on something useful. Writing a PHP script to run any SQL script could take you weeks.

最简单的解决方案是使用shell_exec()以SQL脚本作为输入运行mysql客户机。这可能会运行得稍微慢一点，因为它需要分叉，但是您可以在几分钟内编写代码，然后再继续处理一些有用的东西。编写一个PHP脚本来运行任何SQL脚本都需要花费数周的时间。

Supporting SQL scripts is more complex than what people are describing here, unless you're certain that your script contains only a subset of the functionality of scripts. Below are some examples of things that may appear in an ordinary SQL script that make it complex to code a script to interpret it line by line.

支持SQL脚本比这里描述的要复杂得多，除非您确定您的脚本只包含脚本功能的一部分。下面是一些可能出现在普通SQL脚本中的示例，这些示例使代码的代码变得复杂，从而使脚本能够逐行解释。

-- Comment lines cannot be prepared as statements
-- This is a MySQL client tool builtin command.  
-- It cannot be prepared or executed by server.
USE testdb;

-- This is a multi-line statement.
CREATE TABLE foo (
  string VARCHAR(100)
);

-- This statement is not supported as a prepared statement.
LOAD DATA INFILE 'datafile.txt' INTO TABLE foo;

-- This statement is not terminated with a semicolon.
DELIMITER //

-- This multi-line statement contains a semicolon 
-- but not as the statement terminator.
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM foo;
END
// 

If you only support a subset of SQL scripts, excluding some corner cases such as those above, it's relatively easy to write a PHP script that reads a file and executes the SQL statements within the file. But if you want to support any valid SQL script, that's much more complex.

如果您只支持SQL脚本的一个子集，不包括上面提到的一些特殊情况，那么编写一个PHP脚本来读取文件并在文件中执行SQL语句是相对容易的。但是如果您想要支持任何有效的SQL脚本，那就更复杂了。

---

See also my answers to these related questions:

请参阅我对这些相关问题的回答:

• Running MySQL *.sql files in PHP
• 运行MySQL *。在PHP中sql文件
• is it possible to call a sql script from a stored procedure in another sql script?
• 是否可以从另一个sql脚本中的存储过程调用sql脚本?
• PHP: multiple SQL queries in one mysql_query statement
• PHP:在一个mysql_query语句中有多个SQL查询

#5

mysqli can run multiple queries separated by a ;

mysqli可以运行由a分隔的多个查询;

you could read in the whole file and run it all at once using mysqli_multi_query()

您可以在整个文件中读取并使用mysqli_multi_query()一次运行所有文件

But, I'll be the first to say that this isn't the most elegant solution.

但是，我会第一个说这不是最优雅的解决方案。

#6

In my projects I've used next solution:

在我的项目中，我使用了next解决方案:

<?php

/**
 * Import SQL from file
 *
 * @param string path to sql file
 */
function sqlImport($file)
{

    $delimiter = ';';
    $file = fopen($file, 'r');
    $isFirstRow = true;
    $isMultiLineComment = false;
    $sql = '';

    while (!feof($file)) {

        $row = fgets($file);

        // remove BOM for utf-8 encoded file
        if ($isFirstRow) {
            $row = preg_replace('/^\x{EF}\x{BB}\x{BF}/', '', $row);
            $isFirstRow = false;
        }

        // 1. ignore empty string and comment row
        if (trim($row) == '' || preg_match('/^\s*(#|--\s)/sUi', $row)) {
            continue;
        }

        // 2. clear comments
        $row = trim(clearSQL($row, $isMultiLineComment));

        // 3. parse delimiter row
        if (preg_match('/^DELIMITER\s+[^ ]+/sUi', $row)) {
            $delimiter = preg_replace('/^DELIMITER\s+([^ ]+)$/sUi', '$1', $row);
            continue;
        }

        // 4. separate sql queries by delimiter
        $offset = 0;
        while (strpos($row, $delimiter, $offset) !== false) {
            $delimiterOffset = strpos($row, $delimiter, $offset);
            if (isQuoted($delimiterOffset, $row)) {
                $offset = $delimiterOffset + strlen($delimiter);
            } else {
                $sql = trim($sql . ' ' . trim(substr($row, 0, $delimiterOffset)));
                query($sql);

                $row = substr($row, $delimiterOffset + strlen($delimiter));
                $offset = 0;
                $sql = '';
            }
        }
        $sql = trim($sql . ' ' . $row);
    }
    if (strlen($sql) > 0) {
        query($row);
    }

    fclose($file);
}

/**
 * Remove comments from sql
 *
 * @param string sql
 * @param boolean is multicomment line
 * @return string
 */
function clearSQL($sql, &$isMultiComment)
{
    if ($isMultiComment) {
        if (preg_match('#\*/#sUi', $sql)) {
            $sql = preg_replace('#^.*\*/\s*#sUi', '', $sql);
            $isMultiComment = false;
        } else {
            $sql = '';
        }
        if(trim($sql) == ''){
            return $sql;
        }
    }

    $offset = 0;
    while (preg_match('{--\s|#|/\*[^!]}sUi', $sql, $matched, PREG_OFFSET_CAPTURE, $offset)) {
        list($comment, $foundOn) = $matched[0];
        if (isQuoted($foundOn, $sql)) {
            $offset = $foundOn + strlen($comment);
        } else {
            if (substr($comment, 0, 2) == '/*') {
                $closedOn = strpos($sql, '*/', $foundOn);
                if ($closedOn !== false) {
                    $sql = substr($sql, 0, $foundOn) . substr($sql, $closedOn + 2);
                } else {
                    $sql = substr($sql, 0, $foundOn);
                    $isMultiComment = true;
                }
            } else {
                $sql = substr($sql, 0, $foundOn);
                break;
            }
        }
    }
    return $sql;
}

/**
 * Check if "offset" position is quoted
 *
 * @param int $offset
 * @param string $text
 * @return boolean
 */
function isQuoted($offset, $text)
{
    if ($offset > strlen($text))
        $offset = strlen($text);

    $isQuoted = false;
    for ($i = 0; $i < $offset; $i++) {
        if ($text[$i] == "'")
            $isQuoted = !$isQuoted;
        if ($text[$i] == "\\" && $isQuoted)
            $i++;
    }
    return $isQuoted;
}

function query($sql)
{
    global $mysqli;
    //echo '#<strong>SQL CODE TO RUN:</strong><br>' . htmlspecialchars($sql) . ';<br><br>';
    if (!$query = $mysqli->query($sql)) {
        throw new Exception("Cannot execute request to the database {$sql}: " . $mysqli->error);
    }
}

set_time_limit(0);

$mysqli = new mysqli('localhost', 'root', '', 'test');
$mysqli->set_charset("utf8");

header('Content-Type: text/html;charset=utf-8');
sqlImport('import.sql');

echo "Peak MB: ", memory_get_peak_usage(true)/1024/1024;

On test sql file (41Mb) memory peak usage: 3.25Mb

在测试sql文件(41Mb)上，内存峰值使用:3.25Mb

#7

My suggestion would be to look at the sourcecode of PHPMyBackup. It's an automated PHP SQL loader. You will find that mysql_query only loads one query at a time, and projects like PHPMyAdmin and PHPMyBackup have already done the hard work for you of parsing the SQL the correct way. Please don't re-invent that wheel :P

我的建议是查看PHPMyBackup的源代码。它是一个自动的PHP SQL加载程序。您将发现，mysql_query一次只加载一个查询，而PHPMyAdmin和PHPMyBackup等项目已经完成了对SQL的正确解析的繁重工作。请不要重新发明*:P

#8

An updated solution of Plahcinski solution. Alternatively you can use fopen and fread for bigger files:

更新的Plahcinski解决方案。你也可以使用fopen和fread来处理更大的文件:

$fp = file('database.sql', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$query = '';
foreach ($fp as $line) {
    if ($line != '' && strpos($line, '--') === false) {
        $query .= $line;
        if (substr($query, -1) == ';') {
            mysql_query($query);
            $query = '';
        }
    }
}

#9

Since I can't comment on answer, beware to use following solution:

由于我无法对答案进行评论，请注意使用以下解决方案:

$db = new PDO($dsn, $user, $password);

$sql = file_get_contents('file.sql');

$qr = $db->exec($sql);

There is a bug in PHP PDO https://bugs.php.net/bug.php?id=61613

PHP PDO中有一个bug: https://bugs.php.net/bug.php?id=61613

db->exec('SELECT 1; invalidstatement; SELECT 2');

won't error out or return false (tested on PHP 5.5.14).

不会出错或返回false(在PHP 5.5.14上测试)。

#10

Works on Navicat dumps. Might need to dump the first /* */ comment navicat puts in.

Navicat转储。可能需要转储第一个/* */注释navicat输入。

$file_content = file('myfile.sql');
$query = "";
foreach($file_content as $sql_line){
  if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
    $query .= $sql_line;
    if (substr(rtrim($query), -1) == ';'){
      echo $query;
      $result = mysql_query($query)or die(mysql_error());
      $query = "";
    }
  }
 }

#11

Try This:

试试这个:

// SQL File
$SQLFile = 'YourSQLFile.sql';

// Server Name
$hostname = 'localhost';

// User Name
$db_user = 'root';

// User Password
$db_password = '';

// DBName
$database_name = 'YourDBName';

// Connect MySQL
$link = mysql_connect($hostname, $db_user, $db_password);

if (!$link) {
die("MySQL Connection error");
}

// Select MySQL DB
mysql_select_db($database_name, $link) or die("Wrong MySQL Database");

// Function For Run Multiple Query From .SQL File
function MultiQuery($sqlfile, $sqldelimiter = ';') {
set_time_limit(0);

if (is_file($sqlfile) === true) {
$sqlfile = fopen($sqlfile, 'r');

if (is_resource($sqlfile) === true) {
$query = array();
echo "<table cellspacing='3' cellpadding='3' border='0'>";

while (feof($sqlfile) === false) {
$query[] = fgets($sqlfile);

if (preg_match('~' . preg_quote($sqldelimiter, '~') . '\s*$~iS', end($query)) === 1) {
$query = trim(implode('', $query));

if (mysql_query($query) === false) {
echo '<tr><td>ERROR:</td><td> ' . $query . '</td></tr>';
} else {
echo '<tr><td>SUCCESS:</td><td>' . $query . '</td></tr>';
}

while (ob_get_level() &gt; 0) {
ob_end_flush();
}

flush();
}

if (is_string($query) === true) {
$query = array();
}
}
echo "</table>";

return fclose($sqlfile);
}
}

return false;
}

/* * * Use Function Like This: ** */

MultiQuery($SQLFile);

#12

Are you sure that its not one query per line? Your text editor may be wrapping lines, but in reality each query may be on a single line.

你确定不是每行都有一个查询吗?您的文本编辑器可能是换行符，但实际上每个查询可能都在一行中。

At any rate, olle's method seems best. If you have reasons to run queries one at time, you should be able to read in your file line by line, then use the semicolon at the end of each query to delimit. You're much better off reading in a file line by line than trying to split an enormous string, as it will be much kinder to your server's memory. Example:

无论如何，奥尔的方法似乎是最好的。如果您有理由一次运行一个查询，您应该能够逐行读取文件，然后在每个查询末尾使用分号分隔。逐行读取文件要比尝试分割一个巨大的字符串好得多，因为这会对服务器的内存更友好。例子:

$query  = '';
$handle = @fopen("/sqlfile.sql", "r");

if ($handle) {
    while (!feof($handle)) {
        $query.= fgets($handle, 4096);

        if (substr(rtrim($query), -1) == ';') {
            // ...run your query, then unset the string
            $query = '';
        }
    }

    fclose($handle);
}

Obviously, you'll need to consider transactions and the rest if you're running a whole lot of queries in a batch, but it's probably not a big deal for a new-install script.

显然，如果要在批处理中运行大量的查询，您需要考虑事务和其他事务，但是对于新安装的脚本来说，这可能不是什么大问题。

#13

mysql_query("LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE mytable");

#14

Unless you plan to import huge .sql files, just read the entire file into memory, and run it as a query.

除非计划导入大型.sql文件，否则只需将整个文件读入内存，并将其作为查询运行。

It's been a while since I've used PHP, so, pseudo code:

我使用PHP已经有一段时间了，伪代码:

all_query = read_file("/my/file.sql")
con = mysql_connect("localhost")
con.mysql_select_db("mydb")
con.mysql_query(all_query)
con.close()

Unless the files are huge (say, over several megabytes), there's no reason to execute it line-at-a-time, or try and split it into multiple queries (by splitting using ;, which as I commented on cam8001's answer, will break if the query has semi-colons within strings)..

除非文件很大(比如，超过几兆字节)，否则没有理由一次一行地执行它，或者尝试将它分割成多个查询(通过使用;分解，正如我在cam8001的回答中所评论的那样，如果查询在字符串中有分号，那么它将会崩溃)。

#15

This The Best Code For restore sql by php can use 100% Goooood! Thank A lot

这是php恢复sql的最好代码，可以使用100% Goooood!感谢很多

$file_content = file('myfile.sql');
$query = "";
foreach($file_content as $sql_line){
if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
 $query .= $sql_line;
 if (substr(rtrim($query), -1) == ';'){
   echo $query;
   $result = mysql_query($query)or die(mysql_error());
   $query = "";
  }
 }
}

#16

The easiest and fastest way to load & parse phpmyadmin dump or mysql dump file..

加载和解析phpadmin mydump或mysql转储文件的最简单、最快的方法。

$ mysql -u username -p -h localhost dbname < dumpfile.sql 

#17

Briefly, the way I have done this is:

简单地说，我这样做的方式是:

1. Read the file (a db dump eg $ mysqldump db > db.sql)

   读取文件(db转储如$ mysqldump db > db.sql)

   $sql = file_get_contents(db.sql);
   

2. Import it using mysqli::multi_query

   进口使用mysqli::multi_query

   if ($mysqli->multi_query($sql)) {
       $mysqli->close();
   } else {
       throw new Exception ($mysqli->error);
   }
   

Watch out mysqli_query supports async queries. More here: http://php.net/manual/en/mysqli.multi-query.php and here https://*.com/a/6652908/2002493

注意mysqli_query支持异步查询。更多信息:http://php.net/manual/en/mysqli.multiquery.php，这里是https://*.com/a/6652908/2002493。

#18

None of the solutions I have seen here deal with needing to change the delimiter while creating a stored procedure on a server where I can't count on having access to LOAD DATA INFILE. I was hoping to find that someone had already solved this without having to scour the phpMyAdmin code to figure it out. Like others, I too was in the process of looking for someone else's GPL'ed way of doing it since I am writing GPL code myself.

我在这里看到的解决方案中，没有一个解决方案涉及在服务器上创建存储过程时需要更改分隔符，在服务器上，我无法指望能够访问加载数据的文件。我希望有人已经解决了这个问题，而不需要搜索phpMyAdmin代码就能解决。和其他人一样，我也在寻找别人的GPL方式，因为我自己正在编写GPL代码。

#19

Some PHP libraries can parse a SQL file made of multiple SQL statements, explode it properly (not using a simple ";" explode, naturally), and the execute them.

一些PHP库可以解析由多个SQL语句组成的SQL文件，正确地(不使用简单的“;”爆炸，自然地)和执行它们。

For instance, check Phing's PDOSQLExecTask

例如，检查Phing的PDOSQLExecTask

#20

Just to restate the problem for everyone:

让我们重申一下这个问题:

PHP's mysql_query, automatically end-delimits each SQL commands, and additionally is very vague about doing so in its manual. Everything beyond one command will yield an error.

PHP的mysql_query会自动终止每个SQL命令，而且在其手册中也非常模糊。除了一个命令之外的所有东西都会产生错误。

On the other mysql_query is fine with a string containing SQL-style comments, \n, \r..

在另一个mysql_query上，可以使用包含sql样式注释的字符串，\n， \r..

The limitation of mysql_query reveals itself in that the SQL parser reports the problem to be directly at the next command e.g.

mysql_query的局限性表明，SQL解析器将问题直接报告到下一个命令，例如。

 You have an error in your SQL syntax; check the manual that corresponds to your
 MySQL server version for the right syntax to use near 'INSERT INTO `outputdb:`
 (`intid`, `entry_id`, `definition`) VALUES...

Here is a quick solution: (assuming well formatted SQL;

这里有一个快速的解决方案:(假设格式良好的SQL;

$sqlCmds = preg_split("/[\n|\t]*;[\n|\t]*[\n|\r]$/", $sqlDump);

#21

Many hosts will not allow you to create your own database through PHP, but you seem to have solved that.
Once the DB has been created, you can manipulate and populate it simply:

许多主机不允许您通过PHP创建自己的数据库，但您似乎已经解决了这个问题。创建DB后，您可以简单地操作和填充它:

mysql_connect("localhost");
mysql_query("SOURCE file.sql");

mysql_connect(“localhost”);mysql_query(“源file.sql”);

#22

Some guys (Plahcinski) suggested this code:

有些人(Plahcinski)建议这样做:

$file_content = file('myfile.sql');
$query = "";
foreach($file_content as $sql_line){
  if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
    $query .= $sql_line;
    if (substr(rtrim($query), -1) == ';'){
      echo $query;
      $result = mysql_query($query)or die(mysql_error());
      $query = "";
    }
  }
 }

but I would update it with the one which worked for me:

但我会更新它与一个对我有用的:

 //selecting my database
    $database = 'databaseTitleInFile';
    $selectDatabase = mysql_select_db($database, $con);
    if(! $selectDatabase )
    {
      die('Could not select the database: ' . mysql_error());
    }
    echo "The database " . $database . " selected successfully\n";
//reading the file
    $file_path='..\yourPath\to\File';
    if(!file_exists($file_path)){
        echo "File Not Exists";
    }
    $file_content = file_get_contents($file_path);
    $array = explode("\n", $file_content)
//making queries
    $query = "";
        foreach($array as $sql_line){
$sql_line=trim($sql_line);
          if($sql_line != "" && substr($sql_line, 0, 2) === "--" && strpos($sql_line, "/*") === false){
            $query .= $sql_line;
            if (substr(rtrim($query), -1) == ';'){
              $result = mysql_query($query)or die(mysql_error());
              $query = "";
            }
          }
         }

because it is more comprehensive. ;-)

因为它更全面。:-)

#23

This may be helpful -->

这可能有帮助——>

More or less what it does is to first take the string given to the function (the file_get_contents() value of your file.sql) and remove all the line breaks. Then it splits the data by the ";" character. Next it goes into a while loop, looking at each line of the array that is created. If the line contains the " ` " character, it will know it is a query and execture the myquery() function for the given line data.

它的作用或多或少是首先获取给函数的字符串(file.sql的file_get_contents()值)，并删除所有的换行符。然后它通过“;”字符分割数据。接下来，它进入一个while循环，查看创建的数组的每一行。如果该行包含“'”字符，它将知道它是一个查询，并对给定的行数据进行查询()。

Code:

代码:

function myquery($query) {

mysql_connect(dbhost, dbuser, dbpass);

mysql_select_db(dbname);

$result = mysql_query($query);

if (!mysql_errno() && @mysql_num_rows($result) > 0) {
}

else {

$result="not";
}
mysql_close();

return $result;

}



function mybatchquery ($str) {

$sql = str_replace("\n","",$str)

$sql = explode(";",$str);

$x=0;

while (isset($str[$x])) {

if (preg_match("/(\w|\W)+`(\w|\W)+) {

myquery($str[$x]);

}

$x++

}

return TRUE;

}




function myrows($result) {

$rows = @mysql_num_rows($result);

return $rows;
}




function myarray($result) {

$array = mysql_fetch_array($result);

return $array;
}




function myescape($query) {

$escape = mysql_escape_string($query);

return $escape;
}



$str = file_get_contents("foo.sql");
mybatchquery($str);

#24

Why not take the code from phpMyAdmin and use that? It's Open Source after all...

为什么不从phmypadmin获取代码并使用它呢?毕竟它是开源的……

#25

I use this all the time:

我一直在用这个:

$sql = explode(";",file_get_contents('[your dump file].sql'));// 

foreach($sql as $query)
 mysql_query($query);

#26

I hope the following code will solve your problem pretty well.

我希望下面的代码能很好地解决您的问题。

//Empty all tables' contents

$result_t = mysql_query("SHOW TABLES");
while($row = mysql_fetch_assoc($result_t))
{
mysql_query("TRUNCATE " . $row['Tables_in_' . $mysql_database]);
}
// 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 = '';
}
}

?>

#27

this actually worked for me:

这实际上对我起了作用:

/* load sql-commands from a sql file */
function loadSQLFromFile($url)
{
    // ini_set ( 'memory_limit', '512M' );
    // set_time_limit ( 0 );

    global $settings_database_name;
    global $mysqli_object; global $worked; $worked = false;

    $sql_query = "";

    // read line by line
    $lines = file($url);
    $count = count($lines);

    for($i = 0;$i<$count;$i++)
    {
        $line = $lines[$i];
        $cmd3 = substr($line, 0, 3);
        $cmd4 = substr($line, 0, 4);
        $cmd6 = substr($line, 0, 6);
        if($cmd3 == "USE")
        {
            // cut away USE ``;
            $settings_database_name = substr($line, 5, -3);
        }
        else if($cmd4 == "DROP")
        {
            $mysqli_object->query($line); // execute this line
        }
        else if(($cmd6 == "INSERT") || ($cmd6 == "CREATE"))
        {
            // sum all lines up until ; is detected
            $multiline = $line;
            while(!strstr($line, ';'))
            {
                $i++;
                $line = $lines[$i];
                $multiline .= $line;
            }
            $multiline = str_replace("\n", "", $multiline); // remove newlines/linebreaks
            $mysqli_object->query($multiline); // execute this line
        }       
    }

    return $worked;
}
?>

#28

I have an environment where no mysql tool or phpmyadmin just my php application connecting to a mysql server on a different host but I need to run scripts exported by mysqldump or myadmin. To solve the problem I created a script multi_query as I mentioned here

我的环境中没有mysql工具或phpadmin应用程序，只是php应用程序连接到另一个主机上的mysql服务器，但是我需要运行mysqldump或myadmin导出的脚本。为了解决这个问题，我创建了一个脚本multi_query

It can process mysqldump output and phpmyadmin exports without mysql command line tool. I also made some logic to process multiple migration files based on timestamp stored in DB like Rails. I know it needs more error handling but currently does the work for me.

它可以在没有mysql命令行工具的情况下处理mysqldump输出和phpmyadmin导出。我还编写了一些逻辑来处理基于存储在DB(比如Rails)中的时间戳的多个迁移文件。我知道它需要更多的错误处理，但目前它为我做了工作。

Check it out: https://github.com/kepes/php-migration

检查一下:https://github.com/kepes/php-migration

It's pure php and don't need any other tools. If you don't process user input with it only scripts made by developers or export tools you can use it safely.

它是纯php，不需要任何其他工具。如果您不使用它处理用户输入，只有开发人员编写的脚本或导出工具，您可以安全地使用它。

#29

I noticed that the PostgreSQL PDO driver does not allow you to run scripts separated by semicolons. In order to run a .sql file on any database using PDO it is necessary to split the statements in PHP code yourself. Here is a solution that seems to work quite well:

我注意到PostgreSQL PDO驱动程序不允许运行由分号分隔的脚本。为了使用PDO在任何数据库上运行.sql文件，您需要自己在PHP代码中拆分语句。这里有一个似乎很有效的解决方案:

https://github.com/diontruter/migrate/blob/master/src/Diontruter/Migrate/SqlScriptParser.php

https://github.com/diontruter/migrate/blob/master/src/Diontruter/Migrate/SqlScriptParser.php

The referenced class has done the trick for me in a database independent way, please message me if there are any issues. Here is how you could use the script after adding it to your project:

引用的类以独立于数据库的方式为我完成了这个技巧，如果有任何问题，请通知我。以下是在将脚本添加到项目后如何使用该脚本:

$pdo = new PDO($connectionString, $userName, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$parser = new SqlScriptParser();
$sqlStatements = $parser->parse($fileName);
foreach ($sqlStatements as $statement) {
    $distilled = $parser->removeComments($statement);
    if (!empty($distilled)) {
        $statement = $pdo->prepare($sql);
        $affectedRows = $statement->execute();
    }
}