如何解決mysql多個(gè)字段update時(shí)錯(cuò)誤使用and連接字段的問題

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

執(zhí)行語句一

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

結(jié)果為只將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)

執(zhí)行語句二

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

三個(gè)字段值都變更為給定值，

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)

執(zhí)行語句三

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

只將第一個(gè)字段變更為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語法為語句二，更新多個(gè)字段的值，多個(gè)字段之間使用逗號(hào)“，”分隔。

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

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

一、問題語句一

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

等價(jià)于

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

等價(jià)于

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

相當(dāng)于將book_id的值更新為下面語句的值

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

該語句由三個(gè)表達(dá)式通過mysql的邏輯運(yùn)算符and連接

表達(dá)式一為:  2

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

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

由于當(dāng)時(shí)unit_id = 55,article_id=55555

表達(dá)一的值為2

表達(dá)式二值為0

表達(dá)式三的值為0

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

的值為2 and 0 and 0 即為。

即執(zhí)行語句的結(jié)果等價(jià)于update spoken set book_id = 0 where id = 284989;

Mysql的邏輯運(yùn)算

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

邏輯與 ( AND 或 && )

(1) 當(dāng)所有操作數(shù)均為非零值、并且不為 NULL 時(shí)，所得值為 1
(2) 當(dāng)一個(gè)或多個(gè)操作數(shù)為 0 時(shí)，所得值為 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

相當(dāng)于將book_id的值更新為下面語句的值

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

該語句由三個(gè)表達(dá)式通過mysql的邏輯運(yùn)算符and連接

表達(dá)式一為:  2

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

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

由于當(dāng)時(shí)unit_id = 14,article_id=47409

表達(dá)一的值為2

表達(dá)式二值為1

表達(dá)式三的值為1

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

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

即執(zhí)行語句的結(jié)果等價(jià)于update spoken set book_id = 1 where id = 284989;

額外的問題：

Mysql如果對(duì)mysql的數(shù)值型如int做匹配時(shí)，unit_id字段和14做匹配時(shí)

如下三個(gè)語句都匹配到結(jié)果

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';

（字符串轉(zhuǎn)數(shù)值會(huì)截取第一個(gè)非數(shù)字前面的數(shù)字）

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)

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