mysql update only_MySQL ON DUPLICATE KEY UPDATE只插入，而不是更新

I have dug through SO questions and none address my specific issue ... I have read the following relevant threads: Here, to no avail.

我已經挖了SO問題而沒有解決我的具體問題...我已經閱讀了以下相關主題：這里，無濟於事。

I have a simple table with the following data:

我有一個包含以下數據的簡單表：

|-----------------------------------------------------------------------------|

| id | contractor_id | section_id | page_id | modified | timestamp |

|-----------------------------------------------------------------------------|

Here is the create statement:

這是create語句：

CREATE TABLE `reusable_page_modified` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`contractor_id` int(11) DEFAULT NULL,

`section_id` int(11) DEFAULT NULL,

`page_id` int(11) DEFAULT NULL,

`modified` int(1) NOT NULL DEFAULT '1',

`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

UNIQUE KEY `id_UNIQUE` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=13844 DEFAULT CHARSET=utf8;

Now I have 4 rows in the db right now, and they are (I'll leave off id and timestamp since they are auto generated):

現在我現在在db中有4行，它們是(我將自動生成id和timestamp)：

|---------------------------------------------------------|

| contractor_id | section_id | page_id | modified |

###########################################################

| 1016 | 309 | 10303 | 0 |

|----------------------------------------------------------

| 1017 | 309 | 10303 | 1 |

|----------------------------------------------------------

| 1073 | 309 | 10303 | 1 |

|----------------------------------------------------------

| 240 | 309 | 10303 | 1 |

|----------------------------------------------------------

I am focusing on the first line where modified is set to 0. What I want to do is set it to 1 if contractor_id, section_id and page_id all exist. If not, enter a new row.

我專注於修改設置為0的第一行。如果contractor_id，section_id和page_id都存在，我想要做的是將其設置為1。如果沒有，請輸入新行。

This is what I have:

這就是我所擁有的：

INSERT INTO `reusable_page_modified`

(contractor_id, section_id, page_id, modified)

VALUES ('1016', '309', '10303', '1')

ON DUPLICATE KEY UPDATE

`section_id` = '309'

AND `page_id` = '10303'

AND `contractor_id` = '1016';

This creates a new row. I think I am not understanding the ON DUPLICATE KEY UPDATE statment the way it was intended. I have read the MySQL documentation Here and still no help. What am I not seeing here?

這會創建一個新行。我想我不是按照預期的方式理解ON DUPLICATE KEY UPDATE語句。我已經閱讀了MySQL文檔，但仍然沒有幫助。我在這里看不到什么？

1 个解决方案

#1

1

You are not far from being correct, but your syntax is a bit off. First, if you want ON DUPLICATE KEY to work when a record with duplicate values for the contractor_id, section_id, and page_id columns is inserted, you will need a unique constraint on these three columns. You can add one using the following:

你離正確的距離不遠，但你的語法有點偏。首先，如果要在插入具有contractor_id，section_id和page_id列的重復值的記錄時希望ON DUPLICATE KEY工作，則需要對這三列進行唯一約束。您可以使用以下內容添加一個：

ALTER TABLE reusable_page_modified

ADD CONSTRAINT u_cnst UNIQUE (contractor_id, section_id, page_id);

Next, for the actual INSERT statement you would use what you have is close, except that you update all the columns which don't require any updating. Instead, leave the three columns alone and instead update the modified column to 1:

接下來，對於實際的INSERT語句，除了更新不需要任何更新的所有列之外，您將使用接近的所有內容。相反，請保留三列，而是將修改后的列更新為1：

INSERT INTO reusable_page_modified

(contractor_id, section_id, page_id, modified)

VALUES ('1016', '309', '10303', '1')

ON DUPLICATE KEY UPDATE

modified = 1