PHP PDO Postgres与Sqlite列类型的count(*) [英] PHP PDO Postgres versus Sqlite column type for count(*)
问题描述
在此SQL语句中使用PHP PDO
Using PHP PDO with this SQL statement
SELECT count(*) FROM my_table;
返回带有Postgres的INTEGER和带有Sqlite3的STRING.也就是说,如果表中有一行,则Postgres返回(int)1
,而Sqlite返回'1'
.
returns an INTEGER with Postgres and a STRING with Sqlite3. That is, if there is one row in the table, Postgres returns (int)1
and Sqlite returns '1'
.
这是预期的还是错误?
如果您想在家中学习,这里有一个演示脚本,我把它放在一起了.当我的PHPUnit测试通过时(使用内存中的Sqlite作为测试装置),我实际上遇到了这个问题,但是我的应用程序使用生产的Postrgres数据库失败了.
In case you want to follow along at home, here's a demonstration script I threw together. I actually encountered this when my PHPUnit tests passed (using in-memory Sqlite as a test fixture) and but my application failed using the production Postrgres database.
<?php
function connect($dsn)
{
try {
$pdo = new \PDO($dsn);
}
catch (\PDOException $e) {
echo 'New PDO failed: ' . $e->getMessage() . PHP_EOL;
exit;
}
return $pdo;
}
function doQuery($pdo, $sql)
{
if ( ($result = $pdo->query($sql)) === false) {
echo "'$sql' failed: " . print_r($pdo->errorInfo(), true) . PHP_EOL;
}
return $result;
}
$pgo = connect('pgsql:host=localhost;dbname=postgres');
$sqo = connect('sqlite::memory:');
doQuery($pgo, 'DROP TABLE IF EXISTS public.foo');
doQuery($pgo, 'CREATE TABLE public.foo ( ii int )');
doQuery($pgo, 'INSERT INTO public.foo VALUES (42)');
doQuery($sqo, "ATTACH DATABASE ':memory:' AS public;") or die();
doQuery($sqo, 'DROP TABLE IF EXISTS public.foo');
doQuery($sqo, 'CREATE TABLE public.foo ( ii int )');
doQuery($sqo, 'INSERT INTO public.foo VALUES (42)');
$pgResult = doQuery($pgo, 'SELECT COUNT(ii) FROM foo');
echo 'Postgres: ';
var_dump($pgResult->fetchColumn());
echo 'Sqlite3: ';
$ltResult = doQuery($sqo, 'SELECT COUNT(ii) FROM foo');
var_dump($ltResult->fetchColumn());
推荐答案
这是sqlite没有数据类型的副作用.或者更确切地说,拥有所谓的动态类型系统.但很有趣
This is a side effect of sqlite not having datatypes. Or rather, having what they call the dynamic type system. But quite interestingly
SELECT TYPEOF(b) FROM ( select count(*) as b from my_table) a;
产生integer
作为输出!所以很明显,从sqlite到php的翻译中丢失了一些东西.但是,这并不重要,因为php '1' + 2
中给出了3.因为我们不要忘记,PHP还是动态类型化的系统.
produces integer
as the output! So clearly something is being lost in translation from sqlite to php. However it doesn't really matter because in php '1' + 2
gives 3. Because let's not forget, PHP is also a dynamic typed system.
这篇关于PHP PDO Postgres与Sqlite列类型的count(*)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!