Clever Geek Handbook

Can I do 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 would like the insert to be ignored when the same combination of company_id, term_type and term already exists. I know that if I have a unique index on one field, when I try to insert a duplicate value, the insertion will be ignored. Is there a way to do the combo I'm trying? Can I use a multi-column index?

I am trying to avoid doing SELECT

to test this combination before every insert. As I am processing hundreds of millions of rows of data in this table.

+3


source to share


2 answers


If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings. For example, without IGNORE, a row that duplicates an existing UNIQUE or PRIMARY KEY index on a table raises a duplicate key error and the statement is aborted. With IGNORE, the row is still not inserted, but no error is thrown.



So, if you have a multi-column primary key - it works.

+4


source


Perhaps something like this:



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


source






More articles:


All Articles