Pdo\Mysql

Pdo\Mysql

The Pdo\Mysql Subclass and Driver Options

PHP 8.4 gave each driver a subclass (Pdo\Mysql, Pdo\Pgsql, Pdo\Sqlite...), returned by PDO::connect() or built with new Pdo\Mysql(...). It holds the MySQL-only constants as Pdo\Mysql::ATTR_* and adds getWarningCount(), and PHP 8.5 deprecates the old PDO::MYSQL_ATTR_* spellings. The option that matters most for large results is ATTR_USE_BUFFERED_QUERY: by default PDO copies the whole result into PHP memory before the first fetch(), while unbuffered mode streams rows from the server as you read them.

An 8.5 deprecation, and buffered versus unbuffered resultsPHP

<?php
$pdo = require 'db.php';                          // PDO::connect() returned a Pdo\Mysql
set_error_handler(fn($no, $msg) => print strstr($msg, ',', true) . "\n", E_DEPRECATED);
$old = PDO::MYSQL_ATTR_FOUND_ROWS;                // the pre-8.4 spelling
$pdo->exec('SET SESSION cte_max_recursion_depth = 300000');
$big = "WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 300000)
        SELECT i, REPEAT('x', 100) AS pad FROM n";
foreach (['buffered' => true, 'unbuffered' => false] as $mode => $flag) {
  $pdo->setAttribute(Pdo\Mysql::ATTR_USE_BUFFERED_QUERY, $flag);
  memory_reset_peak_usage();
  $rows = 0;
  foreach ($pdo->query($big) as $row) $rows++;
  printf("%-10s %d rows, peak %4.1f MB\n", $mode, $rows, memory_get_peak_usage() / 2**20);
}

Output

Constant PDO::MYSQL_ATTR_FOUND_ROWS is deprecated since 8.5
buffered   300000 rows, peak 38.1 MB
unbuffered 300000 rows, peak  0.5 MB

Streaming used half a megabyte instead of 38, the difference between an export that finishes and one that hits memory_limit. The price: until an unbuffered result is fully read or closed with closeCursor(), the connection runs nothing else (error 2014, "Cannot execute queries while other unbuffered queries are active"), so use a second connection for lookups during an export. Among the other options, ATTR_MULTI_STATEMENTS is on by default, so exec('DO 1; DO 2') runs both; pass false so an injected ; DROP TABLE cannot run.

MENU