在PHP中运行mysql*.sql文件

这个问题不时出现。没有好的解决方案可以直接从PHP运行.sql脚本。有些边缘情况下.sql脚本中常见的语句不能作为sql语句执行。例如,mysql工具 builtin commands MySQL服务器无法识别的,例如 CONNECT , TEE , STATUS 和 DELIMITER .

所以我给@ignacio vazquez abrams的+1 answer . 您应该通过调用 mysql 工具,例如 shell_exec() .

我做了这个测试:

$command = "mysql --user={$vals['db_user']} --password='{$vals['db_pass']}' "
 . "-h {$vals['db_host']} -D {$vals['db_name']} < {$script_path}";

$output = shell_exec($command . '/shellexec.sql');

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

• Loading .sql files from within PHP
• is it possible to call a sql script from a stored procedure in another sql script?
• PHP: multiple SQL queries in one mysql_query statement

$commands = file_get_contents($location);   
$this->_connection->multi_query($commands);

我用的是:

function run_sql_file($location){
    //load file
    $commands = file_get_contents($location);

    //delete comments
    $lines = explode("\n",$commands);
    $commands = '';
    foreach($lines as $line){
        $line = trim($line);
        if( $line && !startsWith($line,'--') ){
            $commands .= $line . "\n";
        }
    }

    //convert to array
    $commands = explode(";", $commands);

    //run commands
    $total = $success = 0;
    foreach($commands as $command){
        if(trim($command)){
            $success += (@mysql_query($command)==false ? 0 : 1);
            $total += 1;
        }
    }

    //return number of successful queries and total number of queries found
    return array(
        "success" => $success,
        "total" => $total
    );
}


// Here's a startsWith function
function startsWith($haystack, $needle){
    $length = strlen($needle);
    return (substr($haystack, 0, $length) === $needle);
}

您需要为此创建一个完整的SQL解析器。我建议你用 mysql 相反,它的命令行工具从PHP外部调用它。

我从未使用过它,但mysqli类有一个多查询方法:

http://php.net/manual/en/mysqli.multi-query.php

我知道我参加聚会很晚,但是 PHP Mini Admin 在一些场合都是救命恩人。它基本上是一个“lite”phpmyadmin,全部包含在一个文件中,因此不需要复杂的安装,只需上传并登录即可。傻瓜!

别忘了 phpMyAdmin . 与MySQL交互的界面非常坚固。

我不知道它是否解决了您的问题,因为我不知道您是否可以直接从代码与它进行交互,但只是想把它抛到一边。

我创建了一个迁移脚本 multi_query . 它可以在不使用mysql命令行工具的情况下处理mysqldump输出和phpmyadmin导出。我还做了一些逻辑,根据存储在类似于db的rails中的时间戳来处理多个迁移文件。我知道它需要更多的错误处理,但目前它为我工作。

过来看: https://github.com/kepes/php-migration

我认为,如果您不使用它来处理用户输入,那么只有开发人员编写的脚本或导出工具才能安全地使用它。

可以使用此脚本运行MySQL脚本文件。当然,您需要设置$hostname、$username、$password、$databasename、$port和$filename。

<?php

function parseScript($script) {

  $result = array();
  $delimiter = ';';
  while(strlen($script) && preg_match('/((DELIMITER)[ ]+([^\n\r])|[' . $delimiter . ']|$)/is', $script, $matches, PREG_OFFSET_CAPTURE)) {
    if (count($matches) > 2) {
      $delimiter = $matches[3][0];
      $script = substr($script, $matches[3][1] + 1);
    } else {
      if (strlen($statement = trim(substr($script, 0, $matches[0][1])))) {
        $result[] = $statement;
      }
      $script = substr($script, $matches[0][1] + 1);
    }
  }

  return $result;

}

function executeScriptFile($fileName, $dbConnection) {
  $script = file_get_contents($scriptFleName);
  $statements = parseScript($script);
  foreach($statements as $statement) {
    mysqli_query($dbConnection, $statement);
  }
}

$hostName = '';
$userName = '';
$password = '';
$dataBaseName = '';
$port = '';
$fileName = '';

if ($connection = @mysqli_connect($hostName, $userName, $password, $dataBaseName, $port)) {
  executeScriptFile($fileName, $connection);
} else {
  die('Can not connect to MySQL');
}

一个建议:

// connect to db.
if (mysql_query("SOURCE myfile.sql")) {

  echo "Hello Sonny";

} 

要从应用程序中执行表生成,您可能需要创建一个PHP文件,当您运行它时,该文件将执行此操作。

$hostname  = "localhost";
$database  = "databasename";
$username  = "rootuser";
$UserPassword  = "password";

$myconnection = mysql_pconnect($hostname, $username , $UserPassword) or trigger_error(mysql_error(),E_USER_ERROR); 
mysql_connect($hostname , $username , $UserPassword ) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());

if ( !$myconnection ){ echo "Error connecting to database.\n";}


$userstableDrop = " DROP TABLE IF EXISTS `users`";
$userstableCreate = " CREATE TABLE IF NOT EXISTS `users` (
`UserID` int(11) NOT NULL,
  `User_First_Name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15" ;

$userstableInsert = "INSERT INTO `users` (`UserID`, `User_First_Name`) VALUES
(1, 'Mathew'),
(2, 'Joseph'),
(3, 'James'),
(4, 'Mary')";

$userstableAlter1 = "ALTER TABLE `users` ADD PRIMARY KEY (`UserID`)";
$userstableAlter2 = " ALTER TABLE `users` MODIFY `UserID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=15";

$createDb_sql = $userstableDrop;
$insertSite = mysql_query($createDb_sql);

$createDb_sql = $userstableCreate;
$insertSite = mysql_query($createDb_sql);

$createDb_sql = $userstableInsert;
$insertSite = mysql_query($createDb_sql);

$createDb_sql = $userstableAlter1;
$insertSite = mysql_query($createDb_sql);

$createDb_sql = $userstableAlter2;
$insertSite = mysql_query($createDb_sql);

echo "Succesful!";
mysql_close($myconnection );

这是我的解决方案,下面的代码解释了它的作用。 其原理是逐行读取文件,构建一个查询并执行其中的每一个。我看到许多解决方案都使用了“文件获取内容”,这不是一个好的解决方案,因为当它将整个文件内容读取到字符串变量时,可能会导致缓冲区问题。 我的解决方案还考虑了触发器的查询。 没有数组分配,注释和空行被剥离。

<?php
 /**
 * Get a connection from database
 * @param type $db_host database hostname
 * @param type $db_user database username
 * @param type $db_password database password
 * @param type $db_name database name
 * @return \PDO
 */
 function get_db_connection($db_host, $db_user, $db_password, $db_name)
{
    $dns = "mysql:host=$db_host;dbname=$db_name";
    try
    {
        return new PDO($dns, $db_user, $db_password);
    } catch (PDOException $ex)
    {
        return null;
    }
}

/**
 * Runs SQL queries from file
 */

 function exec_sql_queries_from_file($script_file, $db_host, $db_user, $db_password, $db_name)
{
    // to increase the default PHP execution time
    set_time_limit ( 60 ); // Max time = 60 seconds

    // Connect to database
    $connection = get_db_connection($db_host, $db_user, $db_password, $db_name);

    // If the connection is acquired
    if($connection != null){

        // Open sql file
        $f = fopen($script_file, 'r');

        // sql query
        $query = '';

        // Default delimiter for queries
        $delimiter = ';';

        // read line by line
        while (!feof($f))
        {           
            $line = str_replace(PHP_EOL, '', fgets($f)); // read a line and remove the end of line character

            /* if the current line contains the key word 'DELIMITER'. Ex: DELIMITER ;; or DELIMITER $$
             * mostly used for TRIGGERS' queries
             */
            if(strpos($line, 'DELIMITER') !== false)
            {
                // change the delimiter and read the next line
                $delimiter = str_replace('DELIMITER ', '', $line);
                continue;
            }   

            // Consider the line as part of a query if it's not empty and it's not a comment line
            if (!empty($line) && !starts_with($line, '/*') && !starts_with($line, '--'))
            {
                // the query hasn't reach its end: concatenate $line to $query if $line is not a delimiter
                $query .= $line !== $delimiter ? $line : '';

                // if the current line ends with $delimiter: end of current query
                if (ends_with($line, $delimiter))
                {                
                    // exec the query
                    $connection->exec($query) or die($connection->errorInfo());
                    // start new query
                    $query = '';
                }
            }                    
        }

        fclose($f);
    }
}

 /**
 * Starts with function
 */
function starts_with($haystack, $needle)
{
    return $haystack{0} === $needle{0} ? stripos($haystack, $needle) === 0 : false;
}

/**
 * Ends with function
 */
function ends_with($haystack, $needle)
{
    $pos = stripos($haystack, $needle);
    return $pos === FALSE ? FALSE : substr($haystack, $pos) === $needle;

}