mySQL UPDATE 查询返回“0 rows受影响”
我有这样的查询：
UPDATE phonecalls
SET Called = "Yes"
WHERE PhoneNumber = "999 29-4655"
我的表是 phonecalls，我有一个名为 PhoneNumber 的列。我想要将名为 Called 的列更新为“yes”。
知道我做错了什么吗？当我返回查询时，它说 0 行受影响。
I have this query:
UPDATE phonecalls
SET Called = "Yes"
WHERE PhoneNumber = "999 29-4655"
My table is phonecalls, I have a column named PhoneNumber. All I want to update is a column named Called to "yes".
Any idea what I am doing wrong? when I return my query it says 0 rows affected.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
如果该值已经存在，mysql将不会更改它，因此将返回“0 rows受影响”。因此，请务必检查
called的当前值If the such value already exists, mysql won't change it and will therefore return "0 rows affected". So be sure to also check the current value of
called我观察到 0 受影响行的另一个原因是：数据类型错误。如果您要更新的列是整数或布尔值，并且您将其设置为字符串，则它将不会更新 - 但您也不会收到错误。
总结本文中的其他策略/想法：
SELECT语句检查您的 WHERE 是否有效并返回结果。Another reason for 0 affected rows that I have observed: wrong data type. If the column you want to update is an integer or boolean, and you set it to a string, it won't be updated - but you will also get no error.
To sum up the other strategies/ideas from this post:
SELECTstatement, whether your WHERE works and returns results.如果值相同，MySQL 将不会更新该行（不会触发任何警告或错误），因此受影响的行计数将为 0。
If the values are the same, MySQL will not update the row (without triggering any warning or error), so the affected row count will be 0.
问题可能是没有
PhoneNumber == "999 29-4655"的记录。尝试这个查询：
如果它没有返回任何内容，则没有匹配的行。
The problem might be that there are no records with
PhoneNumber == "999 29-4655".Try this query:
If it doesn't return anything, then there are no rows that match.
为了 Google 的任何人的利益，这个问题是由我引起的，因为我试图使用
CONCAT()附加到一个空字段。如果
data为NULL，则CONCAT()返回NULL（忽略第二个参数），因此该值不不更改（将NULL值更新为NULL值），因此更新了 0 行。在这种情况下，更改为
CONCAT_WS()函数反而解决了问题。For the benefit of anyone here from Google, this problem was caused by me because I was trying to append to an empty field using
CONCAT().If
dataisNULL, thenCONCAT()returnsNULL(ignoring the second parameter), so the value does not change (updating aNULLvalue to be aNULLvalue), hence the 0 rows updated.In this case changing to the
CONCAT_WS()function instead fixed the problem.尝试
select count(*) from phonecalls where PhoneNumber = "999 29-4655";这将为您提供匹配行的数量。如果结果为 0，则数据库中没有匹配的行。-Try
select count(*) from phonecalls where PhoneNumber = "999 29-4655";That will give you the number of matching rows. If the result is 0, then there isn't a row in the database that matches.-检查以确保返回一些结果。
如果它没有返回任何结果，则说明过滤器
WHERE PhoneNumber = '999 29-4655'不正确。Check to make sure this returns some result.
If it doesn't return any result than the filter
WHERE PhoneNumber = '999 29-4655'is not correct.这是我的建议：
并确保表和字段的名称区分大小写。
That's my sugestion:
And make sure with the case-sensitive name of table and field`s.
刚刚遇到了一个不起眼的案例。我们的代码从数据库中读取记录列表，更改一列，然后将它们一一写回。
UPDATE的WHERE子句仅包含两个条件：WHERE key=?并且last_update_dt=？。 （时间戳检查用于乐观锁定：如果在我们写入记录之前记录被另一个进程更改，则更新 0 行并引发错误。）但是对于某一特定行，
UPDATE失败 -影响零行。经过一番绞尽脑汁后，我注意到该行的时间戳是
2019-03-10 02:59。在美国大部分地区，该时间戳不存在 - 夏令时导致时间直接从 2:00 跳到 3:00。所以我猜测，在从 MySQL 到 Java 再到 MySQL 的往返过程中，代码的某些部分对时间戳的解释与其他部分不同，导致WHERE子句中的时间戳不匹配。将行的时间戳更改一小时可以避免该问题。
（当然，正确的解决方法是废除夏令时。我创建了 Jira，但美国政府尚未对此做出回应。）
Just ran into an obscure case of this. Our code reads a list of records from the database, changes a column, and writes them back one by one. The
UPDATE'sWHEREclause contains only two conditions:WHERE key=? AND last_update_dt=?. (The timestamp check is for optimistic locking: if the record is changed by another process before we write ours, 0 rows are updated and we throw an error.)But for one particular row the
UPDATEwas failing- zero rows effected.After much hair-pulling I noticed that the timestamp for the row was
2019-03-10 02:59. In much of the U.S. that timestamp wouldn't exist- Daylight Savings Time causes the time to skip directly from 2:00 to 3:00. So I guessed that during the round trip from MySQL to Java back to MySQL, some part of the code was interpreting that timestamp differently from the rest, making the timestamps in theWHEREclause not match.Changing the row's timestamp by one hour avoided the problem.
(Of course, the correct fix is to abolish Daylight Savings Time. I created a Jira but the U.S. Government has not responded to it yet.)
就我而言，我试图更新一列文本以纠正截断问题。尝试更新为正确的文本会产生
0 rows Updated，因为该行中的文本没有更改。一旦我扩展了表结构中的列以容纳正确的字符数，我就能够看到所需的结果。
In my case, I was trying to update a column of text to correct a truncation problem with it. Trying to update to the correct text was yielding
0 rows updatedbecause the text in the row wasn't changing.Once I extended the column in the table structure to accommodate for the correct number of characters, I was able to see the desired results.