MySQL 如何打印 Perl 的 DBI 填充占位符后执行的 SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1687261/
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
How can I print the SQL query executed after Perl's DBI fills in the placeholders?
提问by aidan
I'm using Perl's DBI module. I prepare a statement using placeholders, then execute the query.
我正在使用 Perl 的 DBI 模块。我使用占位符准备一个语句，然后执行查询。
Is it possible to print out the final query that was executed without manually escaping the parameters and dropping them into the placeholders?
是否可以在不手动转义参数并将它们放入占位符的情况下打印出执行的最终查询？
Thanks
谢谢
采纳答案by Sinan ünür
See Tracing in DBI. The following works using DBD::SQLitebut produces a lot of output:
请参阅DBI 中的跟踪。以下使用DBD::SQLite但会产生大量输出：
$dbh->trace($dbh->parse_trace_flags('SQL|1|test'));
Output:
输出：
<- prepare('SELECT ... FROM ... WHERE ... = ?')= DBI::st=HASH(0x21ee924) at booklet-excel.pl line 213
<- prepare('SELECT ... FROM ... WHERE ... = ?')= DBI::st=HASH(0x21ee924) at booklet-excel.pl line 213
<- execute('Inhaler')= '0E0' at booklet-excel.pl line 215
<- execute('Inhaler')= '0E0' at booklet-excel.pl line 215
etc etc.
等等等等
You could plug your own filter in to the trace streamto only keep prepares.
您可以将自己的过滤器插入跟踪流以仅保留prepares。
回答by Upendra
You can do a debug print of a prepared statement using the Statementattribute. This can be accessed either with a "statement handle" or a "database handle".
您可以使用Statement属性对准备好的语句进行调试打印。这可以通过“语句句柄”或“数据库句柄”访问。
print $sth->{Statement} # with a statement handle
print $dbh->{Statement} # with a database handle
回答by masto
Not in general, because DBI doesn't necessarily produce such a query. If your database supports prepared statements and placeholders in its API, DBI will pass them through and let the database do the work, which is one of the reasons to use prepared statements.
一般不会，因为 DBI 不一定会生成这样的查询。如果您的数据库在其 API 中支持预准备语句和占位符，DBI 将通过它们并让数据库完成工作，这也是使用预准备语句的原因之一。
回答by rjh
This works for DBD::mysqlwith server-side prepare disabled (the default):
这适用于DBD::mysql禁用服务器端准备（默认）：
$ DBI_TRACE=2 perl your-script-here
It will print each statement twice, once before binding parameters and once after. The latter will be well-formed SQL that you can run yourself.
它将打印每个语句两次，一次在绑定参数之前，一次在绑定参数之后。后者将是格式良好的 SQL，您可以自己运行。
There is also a module, DBI::Log, which only prints SQL statements (no other debug noise), and optional timing information and caller stacktraces. It's really useful.
还有一个模块，DBI::Log，它只打印 SQL 语句（没有其他调试噪音），以及可选的计时信息和调用者堆栈跟踪。它真的很有用。
回答by bohica
As masto says in general the placeholders in the SQL are not directly replaced with your parameters. The whole point of parameterized SQL is the SQL with placeholders is passed to the database engine to parse once and then it just receives the parameters.
正如masto所说，一般情况下SQL中的占位符不会直接替换为您的参数。参数化 SQL 的全部意义在于将带有占位符的 SQL 传递给数据库引擎进行一次解析，然后它只接收参数。
As idssl notes you can obtain the SQL back from the statement or connection handle and you can also retrieve the parameters from ParamValues. If you don't want to do this yourself you can use something like DBIx::Log4perlto log just the SQL and parameters. See DBIX_L4P_LOG_DELAYBINDPARAM which outputs something like this:
正如 idssl 所指出的，您可以从语句或连接句柄中获取 SQL，还可以从ParamValues 中检索参数。如果您不想自己执行此操作，则可以使用DBIx::Log4perl 之类的工具来仅记录 SQL 和参数。请参阅 DBIX_L4P_LOG_DELAYBINDPARAM 输出如下内容：
DEBUG - prepare(0.1): 'insert into mje values(?,?)'
DEBUG - $execute(0.1) = [{':p1' => 1,':p2' => 'fred'},undef];
Of course as it uses Log::Log4perl you can omit the "DEBUG - " if you want. There is a small tutorial for using DBIx::Log4perl here.
当然，当它使用 Log::Log4perl 时，您可以根据需要省略“DEBUG -”。有使用DBIx :: Log4perl一个小教程在这里。
You should be able to use DBIx::Log4perl with any DBD and if you cannot for some reason RT it and I will look at it.
您应该能够将 DBIx::Log4perl 与任何 DBD 一起使用，如果由于某种原因您不能 RT 它，我会查看它。
If you don't want to go with DBIx::Log4perl and the DBI trace options don't suit your needs you can write callbacks for DBI's prepare/select*/execute methods and gather whatever you like in them.
如果您不想使用 DBIx::Log4perl 并且 DBI 跟踪选项不适合您的需要，您可以为 DBI 的 prepare/select*/execute 方法编写回调并收集您喜欢的任何内容。
回答by not2qubit
If you don't want to create your own tracer module (as suggested by Sinan), you are better off just trying to print the argument hash before it is passed to $sth->execute(). This is especially true, since the "Trace" functionality is DBMS dependent and $sth->{Statement}only returns the SQL placeholder statement. Here's what I did.
如果您不想创建自己的跟踪器模块（如思南所建议的那样），最好在将参数哈希传递给$sth->execute(). 尤其如此，因为“跟踪”功能依赖于 DBMS，并且$sth->{Statement}只返回 SQL 占位符语句。这就是我所做的。
...
while (my $row = $csv->getline_hr($fh)) {
my $cval = "";
my $tquery = $query;
foreach my $j (@cols) {
$cval = $row->{$j};
$tquery =~ s/\?/\'$cval\'/;
}
print "$tquery\n\n";
$rc = $sth->execute(@{$row}{@cols});
}
Where I have used Text::CSV... NOTE: This is not exact, due to DBMS implementation dependent handling of {'}s.
我在哪里使用了 Text::CSV... 注意：这并不准确，因为 {'} 的 DBMS 实现相关处理。
回答by ?mega
For the majority of queries, the simplest debugging is to use the following...
对于大多数查询，最简单的调试是使用以下...
If you prepare and execute a single statement using
domethod, use:use feature 'say'; say $dbh->{Statement};If you use
prepareandexecutemethods separately, use:use feature 'say'; use Data::Dumper; say $sth->{Statement}; say Dumper($sth->{ParamValues});
如果您使用
do方法准备和执行单个语句，请使用：use feature 'say'; say $dbh->{Statement};如果单独使用
prepare和execute方法，请使用：use feature 'say'; use Data::Dumper; say $sth->{Statement}; say Dumper($sth->{ParamValues});
