区分“不影响任何行"和成功更新的行-值相同(MySQL和PHP) [英] Differentiate between 'no rows were affected' and rows succesfully UPDATEd--to same value (MySQL and PHP)

问题描述

我正在从PHP执行SQL(MySQL)命令.每次执行都有几种可能的结果:

I am executing SQL (MySQL) commands from PHP. There are several possible outcomes to each execution:

1. 记录已更新为新值
2. 记录已更新，但值恰好相同
3. 记录找不到要更新的行(即，没有与WHERE子句匹配的行)

1. Record updated to new value
2. Record updated, but values happen to be the same
3. Record finds no rows to update (ie, no rows match the WHERE clause)

我想知道如何区分#的1和3:这两种情况都会随着受影响的行数返回零，所以:

I am wondering how to differentiate between #'s 1 and 3: both cases return zero as the number of rows being affected, so:

$result = $db->exec($statement)

在两种情况下，

将具有$result == 0.我该如何区分?

will have $result == 0 in both cases. How can I tell the difference?

编辑:我的意思是要问如何区分场景2和3，而不是1和3！抱歉给您带来不便...

I meant to ask how to differentiate between scenarios TWO and 3, not 1 and 3! Sorry for the inconvenience...

推荐答案

一个简单的解决方案是两个查询.

A simple solution would be two queries.

首先，使用mysqli_num_rows()运行SELECT查询以检查该行是否存在.

First, run a SELECT query to check if the row exists using mysqli_num_rows().

然后，如果该行存在，则可以运行UPDATE查询并使用mysqli_affected_rows().

Then, if the row exists, you can run the UPDATE query and use mysqli_affected_rows().

...对于任何寻求一个电话的人，我都会建议一个潜在的替代方法.我不知道您是否有兴趣进行任何INSERT或纯粹的UPDATE.以下是一些值得深思的地方:

...I'll suggest a potential alternative for anyone seeking out a single call. I don't know if you are interested in doing any INSERTs, or purely UPDATEs. Below is some food for thought:

在顶部评论中@ http://php.net/manual/en/mysqli.affected-rows.php :

在在重复键更新中插入到查询中"查询中，尽管成功的查询可能希望受影响的行每行仅返回0或1，但实际上可能返回2.

On "INSERT INTO ON DUPLICATE KEY UPDATE" queries, though one may expect affected_rows to return only 0 or 1 per row on successful queries, it may in fact return 2.

从Mysql手册中:使用ON DUPLICATE KEY UPDATE，如果将行作为新行插入，则每行的受影响行值为1；如果更新现有行，则受影响的行值为2."

From Mysql manual: "With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated."

请参阅: http://dev.mysql. com/doc/refman/5.0/en/insert-on-duplicate.html

这是每行的总细目:

+0:未更新或插入行(可能是因为该行已存在，但在UPDATE期间实际上未更改任何字段值)

+0: a row wasn't updated or inserted (likely because the row already existed, but no field values were actually changed during the UPDATE)

+1:已插入一行

+2:行已更新

您能满足自己的需求吗?

Can you make that suit your needs?

这篇关于区分“不影响任何行"和成功更新的行-值相同(MySQL和PHP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！