One, foreword

Recently, I often encounter development error deletion error update data, this is not, they gave me a trouble, let’s look at the whole process.

Second, the process

Because development requires data to be fixed in production, 120 SQL statements need to be executed and the data needs to be updated

The development is connected to the production database, and the first SQL is executed

Update tablename set source_name = "bj1062 "where source_name = "bj1062"Copy the code

There is no problem with this SQL statement. The “where” condition is normal. The general idea is to add string Bj1062 before this address. Yes, there are no mistakes. After the development execution was complete, the results were indeed as expected.

The development then executes the rest of the SQL, updating the address as above. When the source_name is 0, the developer calls me and says:

Harvey, I have performed update where conditions are correct and set values are correct, but all the fields after set are changed to 0. Please help me to see if I can restore the data.

SQL > update tablename set source_name=0; SQL > update tablename set source_name=0

Quickly and development to determine the operation time point, generate flashback SQL, data recovery, while retaining on-site evidence.

Then check the SQL executed by the development and find some very weird SQL:

The SQL quotes are placed after the name of the WHERE field.

update tbl_name set str_col="xxx" = "yyy"
Copy the code

So how does this SQL translate semantics in MySQL? Could it be something like this?

update tbl_name set (str_col="xxx" )= "yyy"
Copy the code

That would be syntactically incorrect, and it would just look like this,

update tbl_name set str_col=("xxx" = "yyy")
Copy the code

while

select "xxx" = "yyy" 
Copy the code

The value of theta is 0, so

update tbl_name set str_col="xxx" = "yyy"
Copy the code

Is equivalent to

update tbl_name set str_col=0
Copy the code

As a result, the source_name field is all updated to 0.

Let’s take a look at what happens with the select form.

mysql [localhost] {msandbox} (test) > select id,str_col from tbl_name where str_col="xxx" = "yyy";
+----+---------+
| id | str_col |
+----+---------+
|  1 | aaa     |
|  2 | aaa     |
|  3 | aaa     |
|  4 | aaa     |
+----+---------+
Copy the code

Select * from str_col=’aaa’ where str_col=’aaa’

mysql [localhost] {msandbox} (test) > warnings Show warnings enabled. mysql [localhost] {msandbox} (test) > explain extended select id,str_col from tbl_name where str_col="xxx" = "yyy"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tbl_name type: index possible_keys: NULL key: Idx_str key_len: 40 ref: NULL rows: 4 filtered: 100.00 Extra: Using WHERE id = 0 Using index 1 row in set, 1 warning (0.00 SEC) Note (Code 1003): /* select#1 */ select `test`.`tbl_name`.`id` AS `id`,`test`.`tbl_name`.`str_col` AS `str_col` from `test`.`tbl_name` where ((`test`.`tbl_name`.`str_col` = 'xxx') = 'yyy')Copy the code

Here he converts the WHERE condition to

((`test`.`tbl_name`.`str_col` = 'xxx') = 'yyy')
Copy the code

The condition first checks whether str_col and ‘XXX’ are equal. If they are, then the parentheses inside are 1; if they are not, then 0. And then 0 or 1 and ‘yyy’,

Since the equals sign is an int on one side and a string on the other, both sides are converted to float for comparison, and ‘yyy’ is converted to float 0, 0 and 0 compare identically to 1

Mysql [localhost] {msandbox} (test) > select 'yyy'+0.0; + -- -- -- -- -- -- -- -- -- -- -- + | 'yyy + 0.0 | + -- -- -- -- -- -- -- -- -- -- -- + | 0 | + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set, 1 Warning (0.00 SEC) mysql [localhost] {msandbox} (test) > select 0=0; + -- -- -- -- -- + | 0 = 0 | + -- -- -- -- -- + | | + -- -- -- -- -- 1 + 1 row in the set (0.00 SEC)Copy the code

The result is constant, which means that the SELECT statement is equivalent to the following SQL

 select id,str_col from tbl_name where 1=1;
Copy the code

All records will be queried.

Third, summary

When writing SQL, you must be careful about whether the quotation marks are in the correct position. Sometimes, the SQL is still normal, but the execution result will be completely wrong. Tests must be performed in the test environment before execution, and problems found in conjunction with IDE syntax highlighting.

Write in the last

Welcome to pay attention to my public number [calm as code], massive Java related articles, learning materials will be updated in it, sorting out the data will be placed in it.

If you think it’s written well, click a “like” and add a follow! Point attention, do not get lost, continue to update!!