PDO SQLite查询零结果问题 [英] PDO SQLite query zero result issue

问题描述

我环顾四周，但似乎找不到任何信息.我不确定这是我的代码问题还是内存SQLite数据库和PDO的已知问题.

I've had a look around but can't seem to find any information on this. I'm not sure if it's an issue with my code or a known issue with in-memory SQLite databases and PDO.

基本上，在将单行插入到内存中的SQLite数据库表后，我希望查询与插入项不匹配的查询返回零行.但是，以下代码仅给出一行(false)，但没有实际的PDO错误代码.

Basically, after inserting a single row into an in-memory SQLite database table, I'd expect that a query that doesn't match the inserted item to return zero rows. However, the following code gives a single row (false) but no actual PDO error code.

<?php

    // Create the DB
    $dbh = new PDO('sqlite::memory:');
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Data we'll be using
    $name = 'Entry';

    // Create DB table
    $dbh->query('
        CREATE TABLE
            Test
            (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(50) NOT NULL
            )
    ');

    // Insert data
    $stmt = $dbh->prepare('
        INSERT INTO
            Test
            (
                name
            )
        VALUES
            (
                :name
            )
    ');

    $stmt->bindParam(':name', $name, PDO::PARAM_STR, 50);
    $stmt->execute();

    // Check data has actually been inserted
    $entries = $dbh->query('
        SELECT
            *
        FROM
            Test
    ')->fetchAll(PDO::FETCH_ASSOC);

    var_dump($entries);

    // Query DB for non-existent items
    $stmt = $dbh->prepare('
        SELECT
            *
        FROM
            Test
        WHERE
            name != :name
    ');

    $stmt->bindParam(':name', $name, PDO::PARAM_STR);
    $stmt->execute();

    // How many rows returned
    echo $stmt->rowCount();

    // Actual data returned
    var_dump($stmt->fetch(PDO::FETCH_ASSOC));
?>

我已经设法解决了一些黑客的问题，但是不必这样做很高兴:

I've managed to work-around the problem with some hackery but it'd be nice to not have to do this:

<?php

    echo (
        (0 == $stmt->rowCount()) || 
        (
            (1 == $stmt->rowCount()) && 
            (false === (($row = $stmt->fetch(PDO::FETCH_ASSOC)))) && 
            ('0000' == array_pop($dbh->errorInfo()))
        )
    ) ? 'true' : 'false';

?>

任何人都可以帮助或指出我可能犯的任何明显错误吗?

Can anyone help or point out any glaring mistakes that I may have made?

推荐答案

PDOStatement: :rowCount()返回

...受相应PDOStatement对象执行的最后一个 DELETE，INSERT或UPDATE 语句影响的行数.

... the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

如果关联的PDOStatement执行的最后一条SQL语句是SELECT语句，则某些数据库可能返回该语句返回的行数.但是，对于所有数据库，不能保证此行为，并且便携式应用程序不应该依赖此行为.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

欢迎来到PDO，那里的简单工作有效，而不太容易的工作毁了您的一天. SQLite是没有可靠的我的结果集中有多少行?"的驱动程序之一.功能.从评论中:

Welcome to PDO, where the easy stuff works and the not-so-easy stuff ruins your day. SQLite is one of the drivers that doesn't have a reliable "how many rows are in my result set?" function. From the comments:

从SQLite 3.x开始，SQLite API本身已更改，现在所有查询都使用语句"实现.因此，PDO无法知道SELECT结果的rowCount，因为SQLite API本身不提供此功能.

As of SQLite 3.x, the SQLite API itself changed and now all queries are implemented using "statements". Because of this, there is no way for PDO to know the rowCount of a SELECT result because the SQLite API itself doesn't offer this ability.

从 PDOStatement :: fetch()false >是什么都不会回来"的保证，并且您的检查代码是完全理智的，即使有点难以阅读.您可能出于自己的理智考虑考虑将PDO和PDOStatement包装或衍生.

A return of false from PDOStatement::fetch() is a guarantee of "nothing came back," and your checking code is entirely sane, if a bit hard to read. You may wish to consider wrapping or deriving from PDO and PDOStatement for your own sanity.

(免责声明:我是PDO迷.)

(Disclaimer: I am a PDO fanboy.)

这篇关于PDO SQLite查询零结果问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！