PHP，MySQL-您能区分匹配的行和受影响的行吗? [英] PHP, MySQL - can you distinguish between rows matched and rows affected?

问题描述

我正在尝试编写一个有点智能的PHP-MySQL数据库处理器.当此处理器决定需要进行更新时，我想报告它是否真的成功.我以为我可以使用mysql_affected_rows ...

// Example:
// After running query "UPDATE mytable SET name='Test' WHERE ID=1"
$result = mysql_affected_rows();
if ($result >= 1) { /* Success */ }

例如，如果不存在具有 ID = 1 的行，则$result将为 0 .

但是，事实证明PHP的mysql_affected_rows是实际受影响的行，如果该行存在但name已经测试"，则可能仍为 0 . ( PHP文档甚至说是这种情况.) /p>

如果我在命令行中运行此命令，则会获得有关查询的以下元信息:

Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

我有什么办法在PHP中获得行匹配"值而不是受影响的行?

[编辑]:我应该知道我可以运行一个单独的查询，但是出于性能考虑，我不想这样做.

解决方案

来自用于mysql_affected_rows的MySQL文档:

对于UPDATE语句，如果您指定 CLIENT_FOUND_ROWS标志何时 连接到mysqld， mysql_affected_rows()返回 WHERE匹配的行数 条款.否则，默认 行为是返回数量 行实际上已更改.

使用 mysqli ，您可以使用

If, for example, there was no row with ID=1, then $result would be 0.

However, it turns out that PHP's mysql_affected_rows is the actual affected rows, and may be still be 0 if the row exists but name was already "Test". (The PHP docs even say this is the case).

If I run this in the command line, I get the following meta information about the query:

Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Is there any way for me to get that "Rows matched" value in PHP instead of the affected rows?

[Edit]: I should note that I know I can run a separate query, but I'd like to not do that, for the sake of performance.

解决方案

From the MySQL documentation for mysql_affected_rows:

For UPDATE statements, if you specify the CLIENT_FOUND_ROWS flag when connecting to mysqld, mysql_affected_rows() returns the number of rows matched by the WHERE clause. Otherwise, the default behavior is to return the number of rows actually changed.

With mysqli, you can specify the CLIENT_FOUND_ROWS using mysqli::real_connect.

$db = mysqli_init();
$db->real_connect('host', 'username', 'password', 'dbname', '3306', null, MYSQLI_CLIENT_FOUND_ROWS);

In PDO, the constant is named PDO::MYSQL_ATTR_FOUND_ROWS

$db = new PDO('mysql:dbname=mydatabase;host=myhost', 'username', 'password', array(
    PDO::MYSQL_ATTR_FOUND_ROWS => true
));

With the old and deprecated MySQL extension, you can specify the CLIENT_FOUND_ROWS passing the value 2 as the 5th parameter for mysql_connect (source).

这篇关于PHP，MySQL-您能区分匹配的行和受影响的行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！