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

This article mainly introduces how to solve the problem of incorrect use of and connection fields when mysql multiple fields update, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Execute statement one

Update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989

The result is that only the value of the book_id field is updated to 0, and no other fields are changed.

Mysql > select id,book_id,unit_id,article_id from spoken

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 5 | 55 | 55555 | |

+-+

1 row in set (0.00 sec)

Mysql > update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select id,book_id,unit_id,article_id from spoken

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 0 | 55 | 55555 | |

+-+

1 row in set (0.00 sec)

Execute statement two

Update spoken set book_id = 2 where id unitless id = 14 Personality id = 47409 Jet = 284989; (normal sentence)

All three field values are changed to a given value

Mysql > select id,book_id,unit_id,article_id from spoken

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 0 | 55 | 55555 | |

+-+

1 row in set (0.00 sec)

Mysql > update spoken set book_id = 2 where id unitless id = 14 where id unqualified id = 47409 unitless id = 284989

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select id,book_id,unit_id,article_id from spoken

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 2 | 14 | 47409 | |

+-+

1 row in set (0.00 sec)

Execute statement three

Update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989

Only change the first field to 1

Mysql > select id,book_id,unit_id,article_id from spoken

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 2 | 14 | 47409 | |

+-+

1 row in set (0.00 sec)

Mysql > update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select id,book_id,unit_id,article_id from spoken

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 1 | 14 | 47409 | |

+-+

1 row in set (0.00 sec)

Analysis.

1. The normal update syntax is statement two, which updates the values of multiple fields and is separated by commas.

2. However, question statement 1 and question statement 3 update the values of multiple fields using and to separate multiple fields.

And statement 1 changes book_id to 1, statement 3 changes book_id to 1

I. question statement one

Update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989

Equivalent to

Update spoken set book_id = (2 and unit_id = 14 and article_id = 47409) where id = 284989

Equivalent to

Update spoken set book_id = (2 and (unit_id = 14) and (article_id = 47409)) where id = 284989

This is equivalent to updating the value of book_id to the value of the following statement

Select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989

The statement is concatenated by three expressions through the logical operator and of mysql

Expression one is: 2

Expression 2 is: unit_id = 14 (select unit_id = 14 from spoken where id = 284989;)

The third expression is: article_id = 47409 (select article_id = 47409 from spoken where id = 284989;)

Due to the fact that unit_id = 55 at that time, it was lethal 55555.

The value of expression one is 2.

The binary value of the expression is 0

The value of expression three is 0

So select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989

Is a value of 2 and 0 and 0.

That is, the result of executing the statement is equivalent to update spoken set book_id = 0 where id = 284989

Logical Operation of Mysql

Http://www.cnblogs.com/pzk7788/p/6891299.html

Logic and (AND or & &)

(1) when all operands are non-zero and not NULL, the resulting value is 1.

(2) when one or more operands are 0, the resulting value is 0.

(3) the value obtained in other cases is NULL.

Mysql > SELECT 1 AND-1, 1 & & 0,0 AND NULL, 1 & & NULL

+-+

| | 1 AND-1 | 1 & & 0 | 0 AND NULL | 1 & & NULL |

+-+

| | 1 | 0 | 0 | NULL |

+-+

Second, sentence three can be obtained in the same way.

2 and unit_id = 14 and article_id = 47409

This is equivalent to updating the value of book_id to the value of the following statement

Select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989

The statement is concatenated by three expressions through the logical operator and of mysql

Expression one is: 2

Expression 2 is: unit_id = 14 (select unit_id = 14 from spoken where id = 284989;)

The third expression is: article_id = 47409 (select article_id = 47409 from spoken where id = 284989;)

Because at that time unit_id = 14 people were responsible for 47409.

The value of expression one is 2.

The binary value of the expression is 1

The value of expression three is 1

So select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989

The value of 2 and 1 and 1 is 1.

That is, the result of executing the statement is equivalent to update spoken set book_id = 1 where id = 284989

Additional questions:

If Mysql matches the numerical type of mysql, such as int, and when the unit_id field matches 14,

The following three statements all match the result

Select id,book_id,unit_id,article_id from spoken where unit_id=14

Select id,book_id,unit_id,article_id from spoken where unit_id='14'

Select id,book_id,unit_id,article_id from spoken where unit_id='14aaa'

(the string value intercepts the number that is not in front of the first number.)

Mysql > select id,book_id,unit_id,article_id from spoken where unit_id=14

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 0 | 14 | 47409 | |

+-+

1 row in set (0.00 sec)

Mysql > select id,book_id,unit_id,article_id from spoken where unit_id='14'

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 0 | 14 | 47409 | |

+-+

1 row in set (0.00 sec)

Mysql > select id,book_id,unit_id,article_id from spoken where unit_id='14aaa'

+-+

| | id | book_id | unit_id | article_id | |

+-+

| | 284989 | 0 | 14 | 47409 | |

+-+

1 row in set, 1 warning (0.00 sec)

Thank you for reading this article carefully. I hope the article "how to solve the problem of incorrect use of and connection fields in mysql multiple fields update" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.