MySQL-PDO查看实际执行SQL的方法？

Question

用PDO来执行一个数据插入，代码如下：

try {
  $DBH = new PDO("mysql:host=localhost;dbname=test",'root','root');
}
catch(PDOException $e) {
    echo $e->getMessage();
}

$DBH->setAttribute( PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING );
$DBH->setAttribute( PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION );

$STH = $DBH->prepare("INSERT INTO `ABCD` (id,title,subject) value (:column1,:column2,:column3)");
$column1 = 100;
$column2 = 'Hello';
$column3 = 'Hello World';
$STH->bindParam(':column1',$column1);
$STH->bindParam(':column2',$column2);
$STH->bindParam(':column3',$column3);

$STH->execute();

Answer 1

这东西简单，我们可以利用异常机制来捕获

 $dbh = new PDO( 'mysql:dbname=test;host=localhost;port=3306', 'root', '' );

$sql = <<<'EOT'
SELECT *
FROM `pdo`
WHERE name = :name;
EOT;
$params = [':name'=>'majie'];

// $sth = $dbh->prepare( $sql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY] );
// $sth ->execute( $params );
// $result = $sth->fetchAll(PDO::FETCH_ASSOC);
// var_dump( $result );

$lastQuery = function() use( $dbh, $sql, $params )
{
$testSql= str_replace( "n", '', '>>>'.$sql.'<<<' );
$sth = $dbh->prepare( $testSql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY] );
$sth ->execute( $params );

preg_match( '/(>>>.*<<<)/', str_replace( "n", '', $sth->errorInfo()[2]), $matches );

return strtr( $matches[0], ['>>>'=>'','<<<'=>''] );
};

echo $lastQuery();

执行结果如下

SELECT * FROM pdo WHERE name = 'majie';

Answer 2

PDO没有现成函数实现你想要的最终执行sql，必须自己定义函数实现了。但是手册上给提供了一个：
public function showQuery($query, $params)

如果想调试sql的话，PDO的PDOStatement::debugDumpParams可以实现sql调试，比如：

 <?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

$sth->debugDumpParams();

?>

 SQL: [96] SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour
Params: 2
Key: Name: [9] :calories
paramno=-1
name=[9] ":calories"
is_param=1
param_type=1
Key: Name: [7] :colour
paramno=-1
name=[7] ":colour"
is_param=1
param_type=2