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

創(chuàng)新互聯(lián)建站是專業(yè)的吉州網(wǎng)站建設(shè)公司，吉州接單;提供做網(wǎng)站、網(wǎng)站建設(shè),網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行吉州網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊,希望更多企業(yè)前來合作!

執(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;（正常語句）

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

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;

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

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

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

該語句由三個表達(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)時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é)果等價于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 時，所得值為 1
(2) 當(dāng)一個或多個操作數(shù)為 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

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

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

該語句由三個表達(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)時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é)果等價于update spoken set book_id = 1 where id = 284989;

額外的問題：

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

如下三個語句都匹配到結(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ù)值會截取第一個非數(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多個字段update時錯誤使用and連接字段的問題”這篇文章對大家有幫助，同時也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!