使用 PDO 获取列类型(getColumnMeta 是 /slow/）

尝试编写一些东西以自动从某个任意数据库结果（即并不总是全部来自表 x）转换为适当的 PHP 类型结果。

我扩展了PDOStatement类，

class Statement extends PDOStatement {
    protected $pdo;
    protected $transformer;
    protected function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->transformer = $pdo->getTransformer();
    }
    public function fetchAll() {
        $results = parent::fetchAll(PDO::FETCH_ASSOC);
        if ($this->getTransformer()) $results = $this->completeResults($results);
        return $results;
    }
    private function completeResults(array $results = []) {
        if ($results == null || count($results) == 0) return null;
        if ($results[0] == false || !is_array($results[0])) return null;
        $index = 0;
        $typeMap = [];
        foreach ($results[0] as $column => $result) {
            $meta = $this->getColumnMeta($index); // this is very painful
            $typeMap[$column] = $meta['native_type'];
            $index++;
        }
        $transformer = $this->getTransformer();
        foreach ($results as $index => &$result) {
            array_walk($result, function(&$value, $key) use ($typeMap, $transformer) {
                $type = $typeMap[$key];
                $value = $transformer->transformToPhpValue($value, $type);
            });
        }
        return $results;
    }
}

以前，在我意识到PDO抽象之前，我正在使用（在我的特定情况下）标准pg_...（） 方法。使用 pg_field_type($resource, $column); ，我可以获取列类型，并且速度相对较快。

现在，使用新的（对我来说）PDO方法。如果我注释掉代码中执行转换的部分，并连续运行 7 个查询：

time to complete: 9.5367431640625E-7 seconds 
time to complete: 1.1920928955078E-6 seconds 
time to complete: 9.5367431640625E-7 seconds 
time to complete: 0 seconds 
time to complete: 9.5367431640625E-7 seconds 
time to complete: 0 seconds 
time to complete: 0 seconds

启用它后：

time to complete: 0.5777850151062 seconds 
time to complete: 0.49124097824097 seconds 
time to complete: 0.28375911712646 seconds 
time to complete: 0.5946729183197 seconds 
time to complete: 0.42177200317383 seconds 
time to complete: 5.0067901611328E-6 seconds 
time to complete: 0.42121982574463 seconds 

那是/疯了/。

我可以通过查看我的 Postgres 日志来判断它正在逐个获取列信息：

LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=1114
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=1114
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=25
... like 30 more of these ...
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=25
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23

查询的复杂性范围从

SELECT 
    p.modified_at, ... ~ 30 fields ..., r.level AS id_level
FROM table_p AS p
LEFT JOIN table_a AS a ON (p.owner = a.id)
LEFT JOIN table_a0 AS a0 ON (p.reporter = a0.id)
LEFT JOIN table_r AS r ON (p.id = r.id)
WHERE (p.id = 1)

只是SELECT * FROM table_a AS a;

所以，我想问题是：有没有更好的方法可以做到这一点？有没有办法在不影响代码速度的情况下做到这一点？7 个查询位于每个请求运行的连续查询的低端，所以这是我想处理的事情。