Why MySQL UPDATE Statements Can Turn All Values to Zero When Quotes Are Misplaced

During a production data‑fix, a developer executed about 120 UPDATE statements to modify the source_name column. The first statement appeared correct:

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

After running the batch, the source_name field of every row became 0 . Inspection of the binlog (using binlog2sql ) revealed many statements of the form:

update tbl_name set str_col="xxx" = "yyy"

Because the quotation marks were placed after the column name, MySQL parsed the expression as a comparison rather than a simple assignment. The parser treats str_col="xxx" as a boolean expression that yields 1 (true) or 0 (false). This boolean result is then compared to the string "yyy" . During the comparison MySQL performs implicit conversion: the string "yyy" is converted to a floating‑point number, which becomes 0 . Consequently the whole condition evaluates to true (since 1 = 0 is false, but 0 = 0 is true), and the assignment is interpreted as str_col = 0 .

Running the same malformed expression in a SELECT statement demonstrates the same behaviour:

select "xxx" = "yyy"

MySQL returns 1 because the inner comparison yields 0 , which is then equal to 0 after conversion. The WHERE clause therefore becomes a constant true condition, causing the UPDATE to affect every row.

Further evidence from EXPLAIN EXTENDED shows the generated condition:

((`test`.`tbl_name`.`str_col` = 'xxx') = 'yyy')

Here the first equality produces a boolean (1 or 0); the second equality forces both sides to float, turning 'yyy' into 0 . The final comparison 0 = 0 is always true, so the UPDATE updates all rows with str_col = 0 .

The incident highlights two important lessons:

Always verify the placement of quotation marks in SQL statements; a misplaced quote can change the semantics without causing a syntax error.

Test potentially destructive statements in a non‑production environment and use IDE syntax highlighting or linting tools to catch such subtle mistakes.

After identifying the faulty statements, the team generated flashback SQL to restore the original data and retained the binlog evidence for post‑mortem analysis.

In summary, MySQL’s implicit type conversion and boolean evaluation can turn a seemingly harmless typo into a data‑loss incident, so careful review and testing are essential.