CODEWITHSUNDEEP
mysqlselectinsertindexing
T. Brian Jones
T. Brian Jones

Reputation: 13522

Can I do an INSERT IGNORE using multiple fields as the IGNORE qualifier in MySQL?

I have a MySQL table that looks like this:

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`company_id` int(8) unsigned NOT NULL,
`term_type` varchar(255) NOT NULL DEFAULT '',
`term` varchar(255) NOT NULL DEFAULT '',

I would like to be able to do this...

INSERT IGNORE INTO table ( company_id, term_type, term )
VALUES( a_company_id, 'a_term_type', 'a_term' )

... but I'd like the insert to be ignored when the same combination of company_id, term_type and term already exists. I am aware that if I have a unique index on a single field when I try to insert a duplicate value, the insert will be ignored. Is there a way to do the combo that I'm attempting? Could I use a multi-column index?

I'm trying to avoid doing a SELECT to check for this combination before every insert. As I'm processing hundreds of millions of rows of data into this table.

Upvotes: 4

Views: 3430

Answers (2)

Arion
Arion

Reputation: 31239

Maybe something like this:

ALTER TABLE table ADD UNIQUE (company_id, term_type,term);

Upvotes: 5

Maksym Polshcha
Maksym Polshcha

Reputation: 18358

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. 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 still is not inserted, but no error is issued.

So if you have a multicolumn primary key - it works.

Upvotes: 4

Related Questions