在 PHP 中运行 MySQL *.sql 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4027769/
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
Running MySQL *.sql files in PHP
提问by Sonny
I have two *.sqlfiles that I use when creating a new web site database. The first file creates all the tables. The second file populates some default records. I would like to execute these files from PHP. I also use the Zend_Framework, if that will help accomplish this.
我*.sql在创建新网站数据库时使用了两个文件。第一个文件创建所有表。第二个文件填充一些默认记录。我想从 PHP 执行这些文件。我也使用 Zend_Framework，如果这有助于实现这一点的话。
Additional Info
附加信息
- I don't have console access
- I'm trying to automate site generation from within our application.
- 我没有控制台访问权限
- 我正在尝试从我们的应用程序中自动生成站点。
SOLUTION
解决方案
Using shell_exec()...
使用shell_exec()...
$command = 'mysql'
. ' --host=' . $vals['db_host']
. ' --user=' . $vals['db_user']
. ' --password=' . $vals['db_pass']
. ' --database=' . $vals['db_name']
. ' --execute="SOURCE ' . $script_path
;
$output1 = shell_exec($command . '/site_db.sql"');
$output2 = shell_exec($command . '/site_structure.sql"');
...I never did get useful output, but followed some suggestionson another threadand finally got it all working. I switch to the --option=valueformat for the commands and used --execute="SOURCE ..."instead of <to execute the file.
...我从来没有得到过有用的输出，但是在另一个线程上遵循了一些建议，最终让它全部工作。我切换到命令的格式并用于而不是执行文件。--option=value--execute="SOURCE ..."<
Also, I never got a good explanation of the difference between shell_exec()and exec().
另外，我从来没有得到的区别的一个很好的解释shell_exec()和exec()。
采纳答案by Bill Karwin
This question comes up from time to time. There's no good solution for running a .sql script directly from PHP. There are edge cases where statements common in a .sql script can't be executed as SQL statements. For example, the mysql tool has builtin commandsthat are not recognized by the MySQL Server, e.g. CONNECT, TEE, STATUS, and DELIMITER.
这个问题不时出现。直接从 PHP 运行 .sql 脚本没有好的解决方案。在某些边缘情况下，.sql 脚本中常见的语句不能作为 SQL 语句执行。例如，MySQL的工具已经内置命令不是由MySQL服务器的认可，例如CONNECT，TEE，STATUS，和DELIMITER。
So I give +1 to @Ignacio Vazquez-Abrams's answer. You should run your .sql script in PHP by invoking the mysqltool, for instance with shell_exec().
所以我给@Ignacio Vazquez-Abrams 的回答+1 。您应该通过调用该mysql工具在 PHP 中运行您的 .sql 脚本，例如使用shell_exec().
I got this test working:
我让这个测试工作：
$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');
See also my answers to these related questions:
另请参阅我对这些相关问题的回答：
回答by Farid Movsumov
$commands = file_get_contents($location);
$this->_connection->multi_query($commands);
回答by Ibrahim Lawal
Here is what I use:
这是我使用的：
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);
}
回答by Ignacio Vazquez-Abrams
You'll need to create a full SQL parser for this. I recommend you use the mysqlcommand line tool for this instead, invoking it externally from PHP.
您需要为此创建一个完整的 SQL 解析器。我建议您mysql为此使用命令行工具，从 PHP 外部调用它。
回答by Dave
I have never had to use it but the mysqli class has a multi_query method:
我从来没有用过它，但 mysqli 类有一个 multi_query 方法：
回答by Matthew
I know I'm pretty late to the party but PHP Mini Adminhas been a lifesaver on a couple of occasions. It's basically a "lite" PHPMyAdmin all contained in one file so no need for complicated installs, just upload it and log in. Simples!
我知道我参加聚会已经很晚了，但PHP Mini Admin有几次是我的救星。它基本上是一个“精简版”PHPMyAdmin，全部包含在一个文件中，因此无需复杂的安装，只需上传并登录即可。简单！
回答by jocull
Don't forget about phpMyAdmin. Pretty solid interface for interacting with MySQL.
不要忘记phpMyAdmin。用于与 MySQL 交互的非常可靠的界面。
I don't know if it solves your problem, since I don't know if you can interact with it directly from code, but just wanted to throw it out there.
我不知道它是否解决了你的问题，因为我不知道你是否可以直接从代码中与它交互，但只是想把它扔在那里。
回答by Sergiy Lavryk
You can use this script to run MySQL script files. You'll need to set $hostName, $userName, $password, $dataBaseName, $port and $fileName of course.
您可以使用此脚本来运行 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');
}
回答by kepes
I created a migration script with 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.
我用multi_query. 它可以在没有 mysql 命令行工具的情况下处理 mysqldump 输出和 phpmyadmin 导出。我还做了一些逻辑来根据存储在像 Rails 一样的 DB 中的时间戳来处理多个迁移文件。我知道它需要更多的错误处理，但目前为我工作。
Check it out: https://github.com/kepes/php-migration
看看：https: //github.com/kepes/php-migration
I think if you don't process user input with it only scripts made by developers or export tools you can use it safely.
我认为，如果您不使用它处理用户输入，则只能使用开发人员或导出工具制作的脚本，您可以安全地使用它。
回答by kizito Ikapel
To execute table generation from within the application, you may want to create a php file that will do just that when you run it.
要从应用程序中执行表生成，您可能需要创建一个 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 );
