在 Perl 中执行 SQL 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4217306/
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
Execute SQL file in Perl
提问by t0mcat
We have a Perl script which runs a SQL and puts data in the table. Now instead of supplying a single SQL statement, we want to pass bunch of them putting them together in a .sql file. We know that our program will fail because it expects a single SQL statement, not s bunch of them (that too from a .sql file). How do we make it work with a .sql file (having multiple INSERT statements?). We are using the DBI package.
我们有一个 Perl 脚本，它运行 SQL 并将数据放入表中。现在，我们不想提供单个 SQL 语句，而是希望将它们一起传递到 .sql 文件中。我们知道我们的程序会失败，因为它需要一个 SQL 语句，而不是一堆 SQL 语句（也来自 .sql 文件）。我们如何使它与 .sql 文件一起工作（有多个 INSERT 语句？）。我们正在使用 DBI 包。
A small snippet of code:
一小段代码：
$sth = $dbh->prepare("/home/user1/tools/mytest.sql");
$sth->execute || warn "Couldn't execute statement";
$sth->finish();
采纳答案by David W.
Not exactly sure what you want...
不完全确定你想要什么......
Once you create a DBI object, you can use it over and over again. Here I'm reading SQL statement after SQL statement from a file and processing each and every one in order:
创建 DBI 对象后，您可以反复使用它。在这里，我从一个文件中读取一个又一个的 SQL 语句，并按顺序处理每一个：
use DBI;
my $sqlFile = "/home/user1/tools/mytest.sql"
my $dbh = DBI::Connect->new($connect, $user, $password)
or die("Can't access db");
# Open the file that contains the various SQL statements
# Assuming one SQL statement per line
open (SQL, "$sqlFile")
or die("Can't open file $sqlFile for reading");
# Loop though the SQL file and execute each and every one.
while (my $sqlStatement = <SQL>) {
$sth = dbi->prepare($sqlStatement)
or die("Can't prepare $sqlStatement");
$sth->execute()
or die("Can't execute $sqlStatement");
}
Notice that I'm putting the SQL statement in the prepareand not the file name that contains the SQL statement. Could that be your problem?
请注意，我将 SQL 语句放在prepare包含 SQL 语句的文件名中，而不是放在文件名中。那可能是你的问题吗？
回答by Cfreak
You don't need perl for this at all. Just use the mysql command line client:
你根本不需要 perl。只需使用 mysql 命令行客户端：
mysql -h [hostname] -u[username] -p[password] [database name] < /home/user1/tools/mytest.sql
mysql -h [hostname] -u[username] -p[password] [database name] < /home/user1/tools/mytest.sql
replace the [variables] with your information.
用您的信息替换 [变量]。
Noteno space after -u or -p. If your mysql server is running on the same machine you can omit -h[hostname] (it defaults to localhost)
注意-u 或 -p 后没有空格。如果您的 mysql 服务器在同一台机器上运行，您可以省略 -h[hostname]（默认为 localhost）
回答by Elvenfighter
There is a sort of workaround for DDL. You need to slurp SQL file first and then enclose it's contents into BEGIN ... END;keywords. Like:
DDL 有一种解决方法。您需要先slurp SQL文件，然后将其内容包含在BEGIN ... END;关键字中。喜欢：
sub exec_sql_file {
my ($dbh, $file) = @_;
my $sql = do {
open my $fh, '<', $file or die "Can't open $file: $!";
local $/;
<$fh>
};
$dbh->do("BEGIN $sql END;");
}
This subroutine allows to run DDL (SQL) scripts with multiple statements inside (e.g. database dumps).
该子例程允许运行包含多个语句的 DDL (SQL) 脚本（例如数据库转储）。
回答by Nickolay Komar
Here is how I've done it. In my case I dont assume one SQL per line and I assume, my example is a bit better :)
这是我如何做到的。就我而言，我不假设每行一个 SQL，我假设，我的示例要好一些 :)
sub get_sql_from_file {
open my $fh, '<', shift or die "Can't open SQL File for reading: $!";
local $/;
return <$fh>;
};
my $SQL = get_sql_from_file("SQL/file_which_holds_sql_statements.sql");
my $sth1 = $dbh1->prepare($SQL);
$sth1->execute();
