如何解決mysql多個字段update時錯誤使用and連接字段的問題

這篇文章主要介紹了如何解決mysql多個字段update時錯誤使用and連接字段的問題，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

執行語句一

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

結果為只將book_id字段值更新為0,其他字段都沒有更改

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)

執行語句二

update spoken set book_id = 2,unit_id = 14,article_id = 47409 where id = 284989;（正常語句）

三個字段值都變更為給定值，

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,unit_id = 14,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 |       2 |      14 |      47409 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

執行語句三

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

只將第一個字段變更為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)

分析，

1、正常的update語法為語句二，更新多個字段的值，多個字段之間使用逗號“，”分隔。

2、但問題語句一和問題語句三更新多個字段的值使用and ，分隔多個字段；

且語句一將book_id變更為，語句三將book_id變更為1；

一、問題語句一

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

等價于

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

等價于

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

相當于將book_id的值更新為下面語句的值

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

該語句由三個表達式通過mysql的邏輯運算符and連接

表達式一為:  2

表達式二為：unit_id = 14 （select unit_id = 14 from spoken where id = 284989;）

表達式三為：article_id = 47409 （select article_id = 47409 from spoken where id = 284989;）

由于當時unit_id = 55,article_id=55555

表達一的值為2

表達式二值為0

表達式三的值為0

所以select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989;

的值為2 and 0 and 0 即為。

即執行語句的結果等價于update spoken set book_id = 0 where id = 284989;

Mysql的邏輯運算

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

邏輯與 ( AND 或 && )

(1) 當所有操作數均為非零值、并且不為 NULL 時，所得值為 1
(2) 當一個或多個操作數為 0 時，所得值為 0 
(3) 其余情況所得值為 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      |
+----------+--------+------------+-----------+

二、同理可得語句三

2 and unit_id = 14 and article_id = 47409

相當于將book_id的值更新為下面語句的值

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

該語句由三個表達式通過mysql的邏輯運算符and連接

表達式一為:  2

表達式二為：unit_id = 14 （select unit_id = 14 from spoken where id = 284989;）

表達式三為：article_id = 47409 （select article_id = 47409 from spoken where id = 284989;）

由于當時unit_id = 14,article_id=47409

表達一的值為2

表達式二值為1

表達式三的值為1

所以select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989;

的值為2 and 1 and 1 即為1。

即執行語句的結果等價于update spoken set book_id = 1 where id = 284989;

額外的問題：

Mysql如果對mysql的數值型如int做匹配時，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='14';

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

（字符串轉數值會截取第一個非數字前面的數字）

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)

感謝你能夠認真閱讀完這篇文章，希望小編分享的“如何解決mysql多個字段update時錯誤使用and連接字段的問題”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!