如何调试 PDO 数据库查询?

2021-12-26 00:00:00 sql php pdo

在转向 PDO 之前，我通过连接字符串在 PHP 中创建了 SQL 查询.如果我遇到数据库语法错误，我可以只回显最终的 SQL 查询字符串，在数据库上自己尝试，然后调整它直到我修复了错误，然后将其放回代码中.

Before moving to PDO, I created SQL queries in PHP by concatenating strings. If I got database syntax error, I could just echo the final SQL query string, try it myself on the database, and tweak it until I fixed the error, then put that back into the code.

准备好的 PDO 语句更快、更好、更安全，但有一件事情困扰着我:我从来没有看到发送到数据库的最终查询.当我在 Apache 日志或自定义日志文件中收到有关语法的错误时(我在 catch 块中记录错误)，我看不到导致它们的查询.

Prepared PDO statements are faster and better and safer, but one thing bothers me: I never see the final query as it's sent to the database. When I get errors about the syntax in my Apache log or my custom log file (I log errors inside a catch block), I can't see the query that caused them.

有没有办法捕获 PDO 发送到数据库的完整 SQL 查询并将其记录到文件中?

推荐答案

在数据库日志中查找

尽管 Pascal MARTIN 是正确的，PDO 不会一次将完整的查询发送到数据库，ryeguy 的建议实际上允许使用 DB 的日志记录功能我可以看到由数据库组装和执行的完整查询.

Looking in the database log

Although Pascal MARTIN is correct that PDO doesn't send the complete query to the database all at once, ryeguy's suggestion to use the DB's logging function actually allowed me to see the complete query as assembled and executed by the database.

方法如下:(这些说明适用于 Windows 机器上的 MySQL - 您的里程可能会有所不同)

Here's how: (These instructions are for MySQL on a Windows machine - your mileage may vary)

  • 在my.ini的[mysqld]部分下，添加一个log命令，例如log="C:Program FilesMySQLMySQL Server 5.1datamysql.log"
  • 重启 MySQL.
  • 它将开始记录该文件中的每个查询.
  • In my.ini, under the [mysqld] section, add a log command, like log="C:Program FilesMySQLMySQL Server 5.1datamysql.log"
  • Restart MySQL.
  • It will start logging every query in that file.

该文件会快速增长，因此请务必在完成测试后将其删除并关闭日志记录.

That file will grow quickly, so be sure to delete it and turn off logging when you're done testing.

相关文章