Menu
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
If you choose Ignore duplicate key, SQL Server will issue a warning message, ignore the offending incoming row and try to insert the remaining rows of the bulk insert operation. If you do not choose Ignore duplicate key, SQL Server will issue an error message and roll back the entire bulk insert operation.
Insert Ignore statement in MySQL has a special feature that ignores the invalid rows whenever we are inserting single or multiple rows into a table. We can understand it with the following explanation, where a table contains a primary key column. The primary key column cannot stores duplicate values into a table.
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 CLIENT_FOUND_ROWS flag is set.
Would suggest NOT using INSERT IGNORE as it ignores ALL errors (ie its a sloppy global ignore).
Instead, since in your example tag is the unique key, use:
INSERT INTO table_tags (tag) VALUES ('tag_a'),('tab_b'),('tag_c')
ON DUPLICATE KEY UPDATE tag=tag;
on duplicate key produces:
Query OK, 0 rows affected (0.07 sec)
Mysql has this handy UPDATE INTO command ;)
edit Looks like they renamed it to REPLACE
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With