Shulou(Shulou.com)05/31 Report--

Editor to share with you how to solve the problem caused by a double quotation mark misplacement in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

I. Preface

Recently, I often encounter developers mistakenly deleting and updating data. No, they have found trouble for me again. Let's take a look at the whole process.

II. Process

Because the developer needs to repair the data in the production process, 120 SQL statements need to be executed and the data needs to be updated.

So the developer connects to the production database and first executes the first SQL

Update tablename set source_name = "bj1062- Beijing Chaoyang District Changying Beichen Fudi" where source_name = "- Beijing Chaoyang District Changying Beichen Fudi"

We took a closer look, this SQL, there is no problem, where conditions are normal, the main idea is to add the string bj1062 in front of this address, is there really no error? Yes, there are no mistakes. After the development execution is completed, the results are indeed in line with expectations.

Then the developer executes the rest of the SQL, which is the same as the SQL above, and updates the address. After the execution was completed, the developer was confused and found that the source_name had become 0. The developer immediately called me and said:

Harvey, I implemented the update,where condition is correct, the value of set is also correct, but all the fields after set have become 0, please help me to see if you can recover the data.

I quickly logged on the server, checked the binlog during this period, found a large number of update tablename set source_name=0 statements, parsed with binlog2sql, project address: binlog2sql

Quickly determine the time of operation with the developer, generate the SQL of flashback, restore the data, and keep the on-site evidence at the same time.

Then check the SQL executed by the development, and find several very strange SQL:

The quotation marks of these SQL go after the name of the where field, and the simplified SQL becomes:

Update tbl_name set str_col= "xxx" = "yyy"

So how does the semantic transformation of this SQL in MySQL?

Could it be something like this?

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

If there is a grammatical error, it will only be in the following form

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

And

Select "xxx" = "yyy"

Is 0, so

Update tbl_name set str_col= "xxx" = "yyy"

Equivalent to

Update tbl_name set str_col=0

So this causes the source_name field to be updated to 0. 0.

Let's take a look at what happens to such a statement in the form of select.

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 |

We found that this SQL also looked up the records of str_col='aaa'. Why?

Mysql [localhost] {msandbox} (test) > warningsShow warnings enabled.mysql [localhost] {msandbox} (test) > explain extended select id,str_col from tbl_name where str_col= "xxx" = "yyy"\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: tbl_name type: index1 row in set 1 warning (0.00 sec) possible_keys: NULLkey: idx_strref: NULLkey_len: 33 rows: 4Extra: Using where Using indexfiltered: 100.00Note (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')

Here he transformed the where condition into

((`test`.`tbl _ name`.`str _ col` = 'xxx') =' yyy')

The first step of this condition is to determine whether str_col and 'xxx' are equal. If they are equal, then the value of parentheses is 1. If not, it is 0.

Then 0 or 1 will judge with 'yyy'.

Since the equal sign is an int on one side and a string on the other, both sides are converted to float for comparison

A case study of query result errors caused by implicit conversion in MySQL

'yyy' is converted to floating-point 0 and 0 is equal to 1.

Mysql [localhost] {msandbox} (test) > select 'yyy'+0.0;+-+ |' yyy'+0.0 | | 0 | +-+ mysql [localhost] {msandbox} (test) > select 00.00 sec +-+ 1 row in set (0.00 sec) | 0,0 | +-+-+ | 1 |

This results in a constant result, that is, the select statement is equivalent to the following SQL

Select id,str_col from tbl_name where 1 # 1

All records will be queried.

The above is all the contents of this article entitled "how to solve the problem caused by a misplaced double quotation mark in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.