PDO SQL 状态“00000"但仍然错误?

2021-12-26 00:00:00 sql database php mysql pdo

谁能解释一下原因

$sql->execute($params);

返回FALSE,而

print $pdo->errorCode();
print_r($pdo->errorInfo());

都返回SQLSTATE 00000,表示根据文档成功?它是一个 INSERT 并且实际上没有将任何内容插入到数据库中...那么,为什么我会从 SQLSTATE 收到成功消息?

both return SQLSTATE 00000, which means according to the documentation success? It is an INSERT and nothing is actually being inserted into the database... so, why do I get a success message from SQLSTATE?

如果有帮助,这是代码...

In case it helps, this is the code...

$sql = $pdo->prepare("
        INSERT INTO user (
            username, fname, pass, salt, email,
            loc_id_home, country_id_home, region_id_home,
            cont_id_home, timestamp_reg, timestamp_upd, timestamp_lastonline, 
            online_status, gender, birthdate
            )
        VALUES (
            :username,:fname,:pass,:random_salt,:email,
            :loc_id_home,:country_id_home,:region_id_home,
            :cont_id_home,'".time()."','".time()."','".time()."',
            1,:gender,:birthdate)
        ");

$params=array(
    ':username'=>$username,
    ':fname'=>$fname,
    ':pass'=>$pass,
    ':random_salt'=>$random_salt,
    ':email'=>$email,
    ':loc_id_home'=>$loc_id_home,
    ':country_id_home'=>$country,
    ':region_id_home'=>$region,
    ':cont_id_home'=>$continent,
    ':gender'=>$gender,
    ':birthdate'=>$birthdate
);  

$sql->execute($params);

print $pdo->errorCode();
print_r($pdo->errorInfo());

推荐答案

这是因为 $pdo->errorInfo() 指的是最后一条成功执行的语句.由于 $sql->execute() 返回 false,所以它不能引用该语句(要么不引用,要么引用之前的查询).

It is because $pdo->errorInfo() refers to the last statement that was successfully executed. Since $sql->execute() returns false, then it cannot refer to that statement (either to nothing or to the query before).

至于为什么 $sql->execute() 返回 false,我不知道...要么是您的 $params 数组有问题,要么与您的数据库连接.

As to why $sql->execute() returns false, I don't know... either there is a problem with your $params array or with your database connection.

PDO::errorCode — 获取与数据库句柄上的最后一个操作相关联的 SQLSTATE

PDO::errorCode — Fetch the SQLSTATE associated with the last operation on the database handle

注意:PHP 手册(http://php.net/manual/en/pdo.errorinfo.php) 并没有准确定义对数据库句柄的最后操作"是什么意思,但是如果绑定参数存在问题,那么该错误就会发生在 PDO 内部,并且不会与数据库进行任何交互.可以肯定地说,如果 $pdo->execute() 返回 true,则 $pdo->errorInfo() 是有效的.如果 $pdo->execute() 返回 false,则 $pdo->errorInfo() 的行为没有从文档.如果我的经验没有记错的话,execute 返回 true,即使 MySQL 返回错误,如果未执行任何操作,则返回 false.由于文档不是特定的,它可能是特定于数据库驱动程序的.

Note: The PHP manual (http://php.net/manual/en/pdo.errorinfo.php) does not define exactly what "last operation on the database handle" means, but if there was an issue with binding parameters, that error would have occurred inside PDO and without any interaction with the database. It is safe to say that if $pdo->execute() returns true, that $pdo->errorInfo() is valid. If $pdo->execute() returns false, the behavior of $pdo->errorInfo() is not explicitly clear from the documentation. If I recall correctly from my experience, execute returns true, even if MySQL returned an error, returns false if no operation was done. Since the documentation is not specific, it might be db driver specific.

此答案反映了截至 2012 年 9 月撰写时的实际经验.正如用户所指出的,文档并未明确重申这种解释.它也可能只反映特定的数据库驱动程序实现,但如果 $pdo->execute() 返回 true,则 $pdo 应该总是正确的->errorInfo() 有效.

This answer reflects practical experience as of when it was written in September 2012. As a user has pointed out, the documentation does not explicitly reaffirm this interpretation. It also may only reflect the particular database driver implementation, but it should always be true that if $pdo->execute() returns true, that $pdo->errorInfo() is valid.

您可能还想在连接序列中设置 PDO::ERRMODE_EXCEPTION.异常处理使得不需要检查和查询错误.

You might also want to set PDO::ERRMODE_EXCEPTION in your connect sequence. Exception handling makes it unnecessary to check and query the error.

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

相关文章