Why Misplaced Quotes in MySQL UPDATE Cause Columns to Be Set to Zero

In a recent production incident, a developer executed a series of UPDATE statements to prepend a prefix to the source_name column, but after the batch ran, every source_name turned into 0 .

Investigation of the binlog revealed that the executed statements were not the intended ones. The original, correct statement looked like:

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

However, a subtle typo moved the closing quotation mark after the WHERE column name, producing malformed SQL such as:

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

MySQL parses this as a boolean expression: it first evaluates "xxx" = "yyy" , which yields 0 , and then assigns that result to str_col . Consequently the column is set to 0 for every row.

Other equivalent malformed forms that lead to the same outcome include:

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

The same expression also appears in SELECT statements, for example:

select "xxx" = "yyy"

Because MySQL treats the equality comparison as an integer (1 for true, 0 for false) and then compares that integer to the string "yyy" , implicit conversion turns "yyy" into 0.0 . The final comparison 0 = 0 is true, so the SELECT returns 1 . When used in a WHERE clause, the condition becomes always true, effectively turning WHERE 1=1 and returning all rows.

This behavior explains why the UPDATE statements unintentionally set source_name to 0 and why a SELECT with the same malformed condition returns all rows.

The article concludes that developers must carefully check the placement of quotation marks in SQL, use IDE syntax highlighting, and always test statements in a non‑production environment before execution.