Lỗi query ok 1 row affected 0.00 sec

INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.

The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
  col=expr
    [, col=expr] ... ]

If more than one unique index is matched, only the first is updated. It is not recommended to use this statement on tables with more than one unique index.

If the table has an AUTO_INCREMENT primary key and the statement inserts or updates a row, the LAST_INSERT_ID() function returns its AUTO_INCREMENT value.

The VALUES() function can only be used in a

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
  col=expr
    [, col=expr] ... ]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
  col=expr
    [, col=expr] ... ]

The IGNORE and DELAYED options are ignored when you use

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
  col=expr
    [, col=expr] ... ]

See Partition Pruning and Selection for details on the PARTITION clause.

This statement activates INSERT and UPDATE triggers. See Trigger Overview for details.

See also a similar statement, REPLACE.

Examples

CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30)); INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');

If there is no existing key, the statement runs as a regular INSERT:

INSERT INTO ins_duplicate VALUES (4,'Gorilla') ON DUPLICATE KEY UPDATE animal='Gorilla'; Query OK, 1 row affected (0.07 sec)

SELECT * FROM ins_duplicate; ++--+

A regular INSERT with a primary key value of 1 will fail, due to the existing key:

INSERT INTO ins_duplicate VALUES (1,'Antelope'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

However, we can use an INSERT ON DUPLICATE KEY UPDATE instead:

INSERT INTO ins_duplicate VALUES (1,'Antelope') ON DUPLICATE KEY UPDATE animal='Antelope'; Query OK, 2 rows affected (0.09 sec)

Note that there are two rows reported as affected, but this refers only to the UPDATE.

SELECT * FROM ins_duplicate; ++--+

Adding a second unique column:

ALTER TABLE ins_duplicate ADD id2 INT; UPDATE ins_duplicate SET id2=id+10; ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);

Where two rows match the unique keys match, only the first is updated. This can be unsafe and is not recommended unless you are certain what you are doing.

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
  col=expr
    [, col=expr] ... ]

Although the third row with an id of 3 has an id2 of 13, which also matched, it was not updated.

Changing id to an auto_increment field. If a new row is added, the auto_increment is moved forward. If the row is updated, it remains the same.