从 PHP 中加载 .sql 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/147821/
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
Loading .sql files from within PHP
提问by Josh Smeaton
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 对其导入命令所做的那样）？
采纳答案by Jeremy Privett
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 或任何它们需要执行的操作来运行每个字符串.
回答by Luis Granja
$db = new PDO($dsn, $user, $password);
$sql = file_get_contents('file.sql');
$qr = $db->exec($sql);
回答by Yasin
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 : [email protected]
*
* $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 < $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 < $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 < $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("/(?<!\\)(\\\\)*\\'/", $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 < $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("/(?<!\\)(\\\\)*\\'/", $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.
mysqli_connect($host,$user,$pass) or die('error connection');
mysqli_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');
}
?>
回答by Bill Karwin
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 脚本的一个子集，不包括上面的一些极端情况，那么编写一个读取文件并执行文件中的 SQL 语句的 PHP 脚本相对容易。但是，如果您想支持任何有效的 SQL 脚本，那就复杂多了。
See also my answers to these related questions:
另请参阅我对这些相关问题的回答：
回答by phatduckk
mysqlican 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.
但是，我会第一个说这不是最优雅的解决方案。
回答by Gromo
In my projects I've used next solution:
在我的项目中，我使用了下一个解决方案：
<?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', '', $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
回答by zstate
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 中有一个错误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 上测试）。
回答by sanneo
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 = '';
}
}
}
回答by SchizoDuckie
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
回答by olle
mysql_query("LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE mytable");
