Why Misplaced Quotes in MySQL UPDATE Can Zero Out Your Data

1. Introduction

Developers often encounter accidental data loss when updating or deleting records. This case study follows a production incident where a series of UPDATE statements unexpectedly set a column to 0.

2. Process

The team executed about 120 UPDATE statements to modify source_name. One of the statements looked correct:

update tablename set source_name = "bj1062-北京市朝阳区常营北辰福第" where source_name = "-北京市朝阳区常营北辰福第";

After running all statements, the source_name column became 0 for every row. Binlog analysis revealed many statements of the form: update tablename set source_name = 0 The root cause was a subtle syntax error: the quotation marks were placed after the column name, producing statements such as: update tbl_name set str_col = "xxx" = "yyy" MySQL parses this as an expression where str_col = "xxx" yields 1 (true) or 0 (false). The result is then compared to the string "yyy". Because MySQL implicitly converts both sides to floating‑point numbers, "yyy" becomes 0, so the whole expression evaluates to 1. Consequently the assignment becomes str_col = 0, overwriting the column with zero.

A similar issue appears in a malformed SELECT:

select id, str_col from tbl_name where str_col = "xxx" = "yyy";

MySQL rewrites the condition as ((str_col = 'xxx') = 'yyy'). The inner comparison yields 1 or 0, which is then compared to 'yyy'. After implicit conversion, the condition is always true, so the query returns all rows.

3. Conclusion

When writing SQL, pay close attention to the placement of quotation marks. Even if the statement parses without error, a misplaced quote can change the semantics dramatically, leading to massive data corruption. Always test statements in a safe environment and use IDE syntax highlighting to catch such mistakes.

Source: For DBA – http://www.fordba.com/mysql-double-quotation-marks-accident.html