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

In a production environment a developer needed to update address information and executed about 120 SQL statements, each intended to prepend a string to the source_name column. The first statement looked correct:

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

After running all statements the source_name field of every row became 0 . By inspecting the binary log with binlog2sql the team discovered many malformed UPDATE statements where the quotation marks were placed after the column name, producing expressions such as:

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

MySQL interprets this as an assignment where the right‑hand side is a comparison expression. The comparison "xxx" = "yyy" evaluates to 0 (false). Because the comparison yields an integer, the whole assignment becomes str_col = 0 , which explains why every row was overwritten with zero.

Further experiments showed other equivalent forms:

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

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

All of them are parsed by MySQL as str_col = ("xxx" = "yyy") . The equality test returns 1 if the strings match, otherwise 0 . When the result is compared again with a string (e.g., ... = "yyy" ), MySQL performs implicit numeric conversion, turning the string "yyy" into 0 , so the final condition is always true and the assignment yields 0 .

The same issue appears in SELECT statements:

select "xxx" = "yyy"

which returns 0 , and when used in a WHERE clause it effectively becomes WHERE 0 = 0 , selecting all rows.

To recover the lost data the team generated flashback SQL statements based on the binlog and restored the original values, preserving evidence for later analysis.

Key takeaways: Always verify the placement of quotation marks in SQL statements; use an IDE with syntax highlighting; test DML changes in a non‑production environment before execution; and understand MySQL's implicit type conversion rules to avoid silent data corruption.