Why Misplaced Double Quotes Turn MySQL Updates Into Zeroes

Introduction

Frequent accidental deletions and updates in production led to a puzzling case where an

UPDATE

statement appeared correct but resulted in the

source_name

column being set to 0 for all rows.

Process

The developer needed to run 120 SQL statements to fix data in production. The first statement looked fine:

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

After executing the first statement the result was as expected, but subsequent similar statements turned the

source_name

field into 0. Binlog analysis revealed many statements like

update tablename set source_name=0

. The malformed SQL had the quotation marks placed after the column name, e.g.:

<code>update tbl_name set str_col="xxx" = "yyy"</code>

MySQL parses this as an assignment where the expression

"xxx" = "yyy"

is evaluated first. Since the comparison yields 0 (both strings are converted to numbers, "yyy" becomes 0), the whole assignment becomes

str_col = 0

, updating every row to zero.

The same issue appears in a

SELECT

statement:

<code>select "xxx" = "yyy"</code>

MySQL evaluates the comparison, converts both sides to floats, and returns 1 because 0 = 0, effectively making the

WHERE

clause always true (

WHERE 1=1

), which returns all rows.

Images illustrating the binlog output and the malformed SQL are retained below:

Conclusion

When writing SQL, ensure quotation marks are placed correctly; a misplaced quote can still produce syntactically valid SQL but change its semantics, leading to disastrous results. Always test statements in a non‑production environment and use IDE syntax highlighting to catch such errors.