mySQL UPDATE 查询返回“0 行受影响”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2186887/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mySQL UPDATE query returns "0 rows affected"
提问by Dan Soap
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 Calledto "yes".
我的表是phonecalls，我有一列名为PhoneNumber. 我只想更新一个名为Called“是”的列。
Any idea what I am doing wrong? when I return my query it says 0 rows affected.
知道我做错了什么吗？当我返回查询时，它说 0 行受影响。
回答by Dan Soap
As amphetamine and Yada suggested, check with a SELECT, if your phone number is in the table.
正如安非他明和亚达所建议的SELECT，如果您的电话号码在表中，请使用 , 进行检查。
But keep in mind: If the value for calledof the row in question is already "Yes", mysql won't change the value and will therefore return "0 rows affected". So be sure to also check the current value of called
但请记住：如果有called问题的行的值已经是“是”，mysql 不会更改该值，因此将返回“0 行受影响”。所以一定要检查当前的值called
回答by Thomas Puppe
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.
我观察到的 0 个受影响行的另一个原因：错误的数据类型。如果您要更新的列是整数或布尔值，并且您将其设置为字符串，则它不会被更新 - 但您也不会收到错误消息。
To sum up the other strategies/ideas from this post:
总结这篇文章中的其他策略/想法：
- Check with a
SELECTstatement, whether your WHERE works and returns results. - Check whether your columns do already have the value you want to set.
- Check if your desired value suits the data type of the column.
- 使用
SELECT语句检查您的 WHERE 是否有效并返回结果。 - 检查您的列是否已经具有您要设置的值。
- 检查您想要的值是否适合列的数据类型。
回答by Malvineous
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().
为了谷歌的任何人的利益，这个问题是由我引起的，因为我试图使用CONCAT().
UPDATE example SET data=CONCAT(data, 'more');
If datais NULL, then CONCAT()returns NULL(ignoring the second parameter), so the value does not change (updating a NULLvalue to be a NULLvalue), hence the 0 rows updated.
如果data是NULL，则CONCAT()返回NULL（忽略第二个参数），因此该值不会更改（将NULL值更新为NULL值），因此更新了 0 行。
In this case changing to the CONCAT_WS()function instead fixed the problem.
在这种情况下，改为改为CONCAT_WS()函数解决了问题。
回答by Jimmy Adaro
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.
如果值相同，MySQL 不会更新该行（不会触发任何警告或错误），因此受影响的行数将为 0。
回答by ryanprayogo
The problem might be that there are no records with PhoneNumber == "999 29-4655".
问题可能是没有带有PhoneNumber == "999 29-4655".
Try this query:
试试这个查询：
SELECT * FROM phonecalls where PhoneNumber = '999 29-4655'
If it doesn't return anything, then there are no rows that match.
如果它不返回任何内容，则没有匹配的行。
回答by Bampfer
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's WHEREclause 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.)
刚刚遇到了一个不起眼的案例。我们的代码从数据库中读取记录列表，更改一列，然后将它们一一写回。本UPDATE的WHERE条款只包含两个条件：WHERE key=? AND last_update_dt=?。（时间戳检查用于乐观锁定：如果记录在我们写入之前被另一个进程更改，则更新 0 行并抛出错误。）
But for one particular row the UPDATEwas failing- zero rows effected.
但是对于一个特定的行，UPDATE失败 - 零行受到影响。
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 the WHEREclause not match.
经过多次梳理后，我注意到该行的时间戳是2019-03-10 02:59. 在美国的大部分地区，时间戳是不存在的——夏令时会导致时间直接从 2:00 跳到 3:00。所以我猜测在从 MySQL 到 Java 再到 MySQL 的往返过程中，代码的某些部分对时间戳的解释与其他部分不同，使得WHERE子句中的时间戳不匹配。
Changing the row's timestamp by one hour avoided the problem.
将行的时间戳更改一小时避免了这个问题。
(Of course, the correctfix is to abolish Daylight Savings Time. I created a Jira but the U.S. Government has not responded to it yet.)
（当然，正确的解决方法是取消夏令时。我创建了一个 Jira，但美国政府尚未对此做出回应。）
回答by Stephen Wylie
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.
就我而言，我试图更新一列文本以纠正它的截断问题。尝试更新为正确的文本是0 rows updated因为行中的文本没有改变。
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.
一旦我扩展了表结构中的列以容纳正确数量的字符，我就能够看到所需的结果。
回答by amphetamachine
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.-
试试select count(*) from phonecalls where PhoneNumber = "999 29-4655";这会给你匹配的行数。如果结果为 0，则数据库中没有匹配的行。-
回答by Yada
Check to make sure this returns some result.
检查以确保这会返回一些结果。
SELECT * FROM phonecalls WHERE PhoneNumber = '999 29-4655'
If it doesn't return any result than the filter WHERE PhoneNumber = '999 29-4655'is not correct.
如果它不返回任何结果，则过滤器WHERE PhoneNumber = '999 29-4655'不正确。
回答by goharma
- Does it say Rows matched: 1 Changed: 0 Warnings: 0? Then maybe it's already set to that value.
- Did you try single quotes vs. double quotes?
- "999 29-4655" is the space a space or a tab and is it consistent in your query and the database?
- 是否说行匹配：1 更改：0 警告：0？那么也许它已经设置为该值。
- 您是否尝试过单引号与双引号？
- “999 29-4655”是空格还是制表符，它在您的查询和数据库中是否一致？
