在PHP中运行MySQL * .sql文件 [英] Running MySQL *.sql files in PHP

问题描述

我有两个在创建新网站数据库时使用的*.sql文件.第一个文件创建所有表.第二个文件填充一些默认记录.我想从PHP执行这些文件.我还使用Zend_Framework，如果这样做将有助于实现这一目标.

其他信息

1. 我没有控制台访问权限
2. 我正在尝试从我们的应用程序中自动生成网站.

解决方案

使用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"');

...我从未得到有用的输出，但遵循了另一个线程上的一些建议最终使一切正常.我将命令切换为--option=value格式，并使用--execute="SOURCE ..."而不是<来执行文件.

此外，我对shell_exec()和exec()之间的区别从来没有得到很好的解释.

解决方案

这个问题有时会出现.没有直接从PHP运行.sql脚本的好的解决方案.在某些情况下，.sql脚本中常见的语句不能作为SQL语句执行.例如，mysql工具具有内置命令，它们是MySQL服务器无法识别，例如CONNECT，TEE，STATUS和DELIMITER.

所以我给+1给@Ignacio Vazquez-Abrams的 answer .您应该通过调用mysql工具在PHP中运行.sql脚本，例如使用 shell_exec() . /p>

---

我测试成功了:

$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');

---

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

• 从PHP内加载.sql文件
• PHP:多个SQL查询合而为一mysql_query语句

I have two *.sql files 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.

Additional Info

1. I don't have console access
2. I'm trying to automate site generation from within our application.

SOLUTION

Using 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 suggestions on another thread and finally got it all working. I switch to the --option=value format for the commands and used --execute="SOURCE ..." instead of < to execute the file.

Also, I never got a good explanation of the difference between shell_exec() and exec().

解决方案

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 commands that are not recognized by the MySQL Server, e.g. CONNECT, TEE, STATUS, and DELIMITER.

So I give +1 to @Ignacio Vazquez-Abrams's answer. You should run your .sql script in PHP by invoking the mysql tool, for instance with 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:

• 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

这篇关于在PHP中运行MySQL * .sql文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！