在 PHP 中运行 MySQL *.sql 文件 [英] Running MySQL *.sql files in PHP
问题描述
我有两个 *.sql 文件，用于创建新的网站数据库.第一个文件创建所有表.第二个文件填充一些默认记录.我想从 PHP 执行这些文件.我也使用 Zend_Framework，如果这有助于实现这一点.
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.
附加信息
- 我没有控制台访问权限
- 我正在尝试从我们的应用程序中自动生成网站.
解决方案
使用 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 ..." 而不是 <执行文件.
...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.
此外，对于shell_exec() 和exec() 之间的区别，我从来没有得到很好的解释.
Also, I never got a good explanation of the difference between shell_exec() and exec().
推荐答案
这个问题不时出现.直接从 PHP 运行 .sql 脚本没有好的解决方案.在某些边缘情况下，.sql 脚本中常见的语句不能作为 SQL 语句执行.例如，mysql 工具具有内置命令，它们是MySQL服务器无法识别，例如CONNECT、TEE、STATUS 和 DELIMITER.
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.
所以我给@Ignacio Vazquez-Abrams 的 answer.您应该通过调用 mysql 工具在 PHP 中运行您的 .sql 脚本，例如使用 shell_exec().
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().
我让这个测试工作了:
$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:
这篇关于在 PHP 中运行 MySQL *.sql 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
