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

This article mainly explains "how to solve the problem of misplacement of a double quotation mark in MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the problem of misplacement of a double quotation mark in MySQL".

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:

I implemented the update,where condition is correct, the value of set is also correct, but all the fields after the set have become 0, you quickly 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 (https://github.com/danfengcao/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) > 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: 33 ref: NULL rows: 4 filtered: 100.00 Extra: Using where 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 `test`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 judge whether str_col and 'xxx' are equal. If so, the value of parentheses is 1. If not, 0 or 1 is then judged with' yyy'. Since the equal sign is int on one side and a string on the other, both sides are converted to float for comparison. You can see my previous article: case study of query result errors caused by implicit conversion in MySQL (https://www.fordba.com/mysql-type-convert-analysis.html))

'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 | +-+ 1 row in set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > select 0010 +-+ | 000.00 | +-+ | 1 | +-+ 1 row in set (0.00 sec)

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 1111

All records will be queried.

Thank you for your reading, the above is the content of "how to solve the problem of misplacement of a double quotation mark in MySQL". After the study of this article, I believe you have a deeper understanding of how to solve the problem of misplacement of a double quote in MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.