Why Misplaced Quotes Turn MySQL UPDATE Results into Zero

1. Introduction

Frequent accidental data deletions and updates in production prompted an investigation into a case where a series of UPDATE statements unexpectedly set a column to zero.

2. Process

The developer needed to fix data in production by executing about 120 UPDATE statements that added a prefix to an address field.

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

The first statement worked as expected, but subsequent similar statements resulted in the source_name column becoming 0. Examination of the binary log revealed many statements of the form: update tbl_name set str_col="xxx" = "yyy" MySQL parses the expression str_col="xxx" = "yyy" as a boolean comparison: it first evaluates str_col="xxx", yielding 1 (true) or 0 (false). That numeric result is then compared to the string "yyy", which MySQL implicitly converts to a floating‑point number ("yyy" becomes 0). Consequently the whole expression evaluates to 0, and the column is set to 0.

Running an equivalent SELECT demonstrates the same behavior: select "xxx" = "yyy" which returns 0 because the string comparison is converted to numeric comparison.

3. Conclusion

When writing SQL, carefully verify the placement of quotation marks; a misplaced quote can turn an UPDATE into a boolean expression that overwrites data with zero. Always test statements in a non‑production environment and use IDE syntax highlighting to catch such errors.