Why Misplaced Double Quotes Turn MySQL UPDATE Results into Zero

Background

During a production data‑fix a developer executed a series of UPDATE statements to prepend a prefix to the source_name column. The first statement succeeded, but after the remaining statements the column values became 0.

Correct UPDATE Example

UPDATE tablename
SET source_name = "bj1062-北京市朝阳区常营北辰福第"
WHERE source_name = "-北京市朝阳区常营北辰福第";

This statement updates the matching rows as expected.

Malformed UPDATE and Its Effect

Some statements were written with the double‑quote placed after the column name, e.g.:

UPDATE tbl_name
SET str_col = "xxx" = "yyy";

MySQL parses the right‑hand side as an expression:

UPDATE tbl_name
SET str_col = ("xxx" = "yyy");

The equality operator returns 1 if the two strings are equal, otherwise 0. The result (0 or 1) is then compared to the string "yyy". Because one operand is numeric and the other is a string, MySQL performs implicit conversion: the string is converted to a floating‑point number. Non‑numeric strings become 0, so the comparison 0 = 0 is true, yielding 1. Consequently the assignment becomes str_col = 0, overwriting the column with zero for every affected row.

SELECT Example with the Same Condition

SELECT id, str_col
FROM tbl_name
WHERE str_col = "xxx" = "yyy";

MySQL rewrites the WHERE clause to: ((`tbl_name`.`str_col` = 'xxx') = 'yyy') The inner comparison 'xxx' = 'yyy' yields 0. The outer comparison 0 = 'yyy' again converts 'yyy' to 0, so the whole condition evaluates to true for every row, returning the entire table.

EXPLAIN Output

EXPLAIN EXTENDED SELECT id, str_col FROM tbl_name
WHERE str_col = "xxx" = "yyy"\G

The output shows type: index and Extra: Using where; Using index, confirming that the rewritten condition is used.

Root Cause Analysis

Misplaced double quotes turn an intended assignment into a boolean expression.

MySQL’s implicit type conversion turns non‑numeric strings into 0, causing the expression to evaluate to 1 and the assignment to become 0.

The same parsing applies to WHERE clauses, making them always true.

Practical Investigation Tools

Binary logs were examined with binlog2sql (GitHub repository: https://github.com/danfengcao/binlog2sql) to reveal the generated UPDATE ... SET source_name = 0 statements.

Recommendations

Always test SQL statements in a non‑production environment before applying them to live data.

Use an IDE or editor with syntax highlighting to spot misplaced quotes.

Review binary logs or EXPLAIN output when unexpected results appear.

Be aware of MySQL’s implicit conversion rules: comparing a numeric result with a non‑numeric string converts the string to 0.