Why Misplaced Quotes Turn MySQL Updates into Zeroes – A Deep Dive

Introduction

Developers often encounter accidental data loss or incorrect updates in production databases. A recent incident showed how a seemingly harmless misplaced double‑quotation mark in an UPDATE statement caused all rows to be set to 0, illustrating the hidden dangers of MySQL's syntax handling.

Process

During a production fix, a developer executed 120 SQL statements to update address data. The first statement looked correct:

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

After running the remaining statements, the source_name column unexpectedly became 0 for every row. Binlog analysis revealed many statements of the form: update tbl_name set str_col="xxx" = "yyy"; Several malformed variations were examined:

update tbl_name set (str_col="xxx") = "yyy";

update tbl_name set str_col=("xxx" = "yyy");

MySQL parses these as: update tbl_name set str_col = 0; The reason is that MySQL evaluates the expression "xxx" = "yyy" first. The comparison yields 0 because the strings differ, and the result (an integer) is then assigned to str_col. When the expression appears in a WHERE clause, MySQL rewrites it as: ((`test`.`tbl_name`.`str_col` = 'xxx') = 'yyy') Here, `str_col` = 'xxx' yields 1 or 0. MySQL then implicitly converts the boolean result and the string 'yyy' to floating‑point numbers; 'yyy' becomes 0, so the whole condition evaluates to 1 (true). Consequently, a query such as:

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

returns all rows because the WHERE clause simplifies to 1=1. The following screenshots illustrate the binlog entries and the MySQL EXPLAIN EXTENDED output:

Conclusion

When writing SQL, the position of quotation marks is critical. Even if a statement parses without syntax errors, misplaced quotes can change the semantics, causing all updates to set fields to 0 or making WHERE clauses always true. Always test statements in a safe environment and use IDE syntax highlighting to catch such subtle errors.