MySQL 更新行为
mysql> 更新表名设置字段名 = 'C200900674' 其中字段名 - 'C200900673';
错误 1062 (23000)：键 1 的条目重复“C200900674-2008-0-1”
对此有什么想法或建议吗？ 我们有人不小心用减号而不是等号进行了更新。 它显然试图更改所有小于该值的记录？ 尽管它是字母数字的并且确实相当不完整。 最重要的是，在出现该错误之前更新了大量记录，但根本没有任何反馈。 没有像“查询正常，X 行受影响（0.00 秒）”这样的内容，所以我们不知道有多少行被更改。 autocommit=1 所以无法回滚。
无论如何，只是寻找有关此的任何提示或指示。 为什么该查询会执行任何操作，它看起来确实应该向我返回一个错误。 当然，除了不让没有经验的管理员做愚蠢的事情的明显答案之外。
mysql> update tablename set fieldname = 'C200900674' where fieldname -
'C200900673';
ERROR 1062 (23000): Duplicate entry 'C200900674-2008-0-1' for key 1
Any thoughts or suggestions on this? We had someone accidentally do that update with a minus sign instead of an equal sign. It apparently tried to change all records less than that value? Even though it's alphanumeric and really fairly incomplete. On top of that, a quantity of records were updated before it got that error and there was no feedback at all. Nothing like "Query OK, X rows affected (0.00 sec)" so we had no idea how many were changed. autocommit=1 so no ability to rollback.
Anyway, just looking for any tips or pointers on this. Why that query did anything at all, it really looks like it should have returned an error to me. Other than the obvious answer of not letting inexperienced admins do boneheaded things of course.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
每当对 Mysql 如何解释 WHERE 子句有疑问时，请将其反转为 SELECT。
并且：
查看第一个选择返回什么值，以及第二个选择找到哪些行。
可悲的是，由于 Mysql 在数字/字符串转换方面相当宽松，特别是在 4.x 系列上，甚至在非严格的 5.x 上，哎呀......即使是严格模式，也很难准确说出出了什么问题。您的 Mysql 配置的详细信息。 这可能是因为 fieldname 被转换为某个数字，“C200900673”也是如此，基本上运行：
这可以翻译为：
无论如何，我希望你有一个备份！
Whenever there's a doubt of how Mysql interpreted a WHERE clause, reverse it to a SELECT.
And:
See what value is returned by the first select, and what rows the second one find.
Sadly, since Mysql is pretty lax on numeric/string conversions, specially on the 4.x series, and even on non-strict 5.x, heck... even strict-moded, it's hard to tell exactly what went wrong without all the details of your Mysql configuration. It could be because fieldname was cast to some number, and so was 'C200900673', basically running:
Which could translate to:
Anyway, I hope you have a backup!
如果您的表使用 InnoDB 存储引擎，则不会造成任何损害。 即使 autocommit=1，查询也只是“全有或全无”执行。
您收到错误消息的事实证明数据库没有触及您的数据。 每当您收到错误时，都会忽略“x rows受影响”消息。
即使没有违反 uniquess 约束，查询也会失败并出现不同的错误：
这是因为减号会导致 MySQL 尝试计算字段内容减去其他值的值。 这是行不通的。 您只是没有看到此错误，因为另一个错误“先到达那里”。
If your table is using the InnoDB storage engine, no harm was done. Even with autocommit=1 the query is only executed "all-or-nothing".
The fact that you got an ERROR message is proof that the DB did not touch your data. Whenever you get an ERROR, the "x rows affected" message is omitted.
Even in case the uniquess constraint would not have been violated the query would have failed with a different error:
This is because the minus sign would have caused MySQL to try and calculate the value of the field contents minus something else. This does not work. You only did not see this error, because the other one "got there first".