MySQL NSERT OR UPDATE + INSERT IGNORE

[Costas]

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE  
name="A", age=19

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. ORA ref

https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/

---

INSERT IGNORE INTO tbl_name (col1,col2) VALUES(15,col1*2);

If you use the IGNORE modifier, ignorable errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead. ORA ref

https://www.mysqltutorial.org/mysql-insert-ignore/

---

CREATE TABLE t4 (
    id int(11) AUTO_INCREMENT,
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `stopDuplication` (col1, col2)
);

now apart the primary key, it is also a UNIQUE constraint..

Sometimes, you want to ensure values in a column or a group of columns are unique. For example, email addresses of users in the users table. You can have multiple UNIQUE. ORA ref

UNIQUE is better to used in combination with INSERT IGNORE

https://www.mysqltutorial.org/mysql-unique-constraint/