为什么使用PHP的sqlite3 fetchAll(PDO:FETCH_ASSOC)将所有数据类型返回为字符串? [英] Why are all data types returned as strings with sqlite3 fetchAll(PDO:FETCH_ASSOC) with PHP?

问题描述

我正在使用

$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

获取表的所有行.

该模式定义为"id INTEGER PRIMARY KEY，标题TEXT，年份INTEGER，价格实数"

The schema is defined as "id INTEGER PRIMARY KEY, title TEXT, year INTEGER, price REAL"

fetchAll结果的一行是

A row from he results of the fetchAll is

array(4) {
    [0]=>
    string(1) "1"
    [1]=>
    string(14) "The Dark Night"
    [2]=>
    string(4) "2008"
    [3]=>
    string(5) "19.95"
  }

为什么所有数据类型都以字符串形式返回?我希望它们按照架构中的定义返回.我了解SQLite的无类型"性质，但是它们确实将有限的数据类型定义为TEXT，INTEGER和REAL.如何获取要使用指定数据类型返回的数据?我不想遍历每一行，并用PHP进行转换-似乎太慢了.

Why are all the data types being returned as strings? I want them to be returned as defined in the schema. I understand the 'typeless' nature of SQLite, but they do define the limited data types as TEXT, INTEGER, and REAL. How can I get the data to be returned with the specified data types? I don't want to iterate through each row, and convert it with PHP - that just seems to be too slow.

完整的测试代码如下:

<?
class sqlite_test {
    function __construct()
    {
        $this->dbase_filename = "test.sqlite";
        $this->init_dbase();

    }

    function init_dbase()
    {
        $this->pdo = new PDO("sqlite:".$this->dbase_filename);
    }

    function open_table()
    {
        $table = "dvds";

        if ( ! ($test_to_see_if_table_exists = $this->pdo->query("SELECT 1 from $table")) ) {
            $schema = "id INTEGER PRIMARY KEY, title TEXT, year INTEGER, price REAL";
            $query = "CREATE TABLE $table ($schema)";

            $this->pdo->exec($query);
        }

        return $test_to_see_if_table_exists;
    }

    function add_test_records()
    {
        $query[]='INSERT INTO dvds (id, title, year, price) VALUES (null, "The Dark Night", 2008, 19.95)';
        $query[]='INSERT INTO dvds (id, title, year, price) VALUES (null, "The Wizard of Oz", 1939, 9.95)';
        $query[]='INSERT INTO dvds (id, title, year, price) VALUES (null, "Jaws", 1977, 6.95)';

        $this->pdo->exec( join(';', $query) );
    }

    function dump_test_records()
    {
        $query = "SELECT * FROM dvds";

        if ($statement = $this->pdo->prepare($query)) {
            $statement->execute();

            $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

            echo "<pre>";
            var_dump($rows);
            echo "</pre>";
        }
    }

    function main()
    {
        if ( ! $this->open_table() ) {
            $this->add_test_records();
        }

        $this->dump_test_records();
    }
}

$test = new sqlite_test();
$test->main();

推荐答案

这与SQLite无关；无论您使用什么查询数据库，所有记录都将以字符串集合的形式返回.这只是PHP使用的数据库适配器/驱动程序的本质.

This has nothing to do with SQLite; all records will be returned as collections of strings no matter what you use to query a database. It's just the nature of the database adapters/drivers used by PHP.

如果希望这些值具有所需的类型，则必须手动进行强制转换.

If you want the values to be of your desired types, you have to cast manually.

这篇关于为什么使用PHP的sqlite3 fetchAll(PDO:FETCH_ASSOC)将所有数据类型返回为字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！