avoiding duplicates in MySQL on mulitple column values

Question

I keep finding myself writing queries to avoid inserting when there are duplicates - things like

select * from foobar where bar=barbar and foo=foofoo

and then checking in PHP with mysql_num_rows() to see if the number of results is > 0 to determine whether to go forward with my insert.

EDIT: for instance, let's say a user wants to send an invitation to another user. I want to make sure that in my invitations table, I don't add another entry with the same pair invited_id AND game_id. so this requires some sort of check.

this feels inefficient (and slightly dirty). is there a better way?

can you pls. elaborate further so I am able to give you proper answer. — Jwalin Shah
– Jwalin Shah, Commented Nov 25, 2011 at 9:50
The obvious answer is a unique index on columns foo, bar - have you considered that? — Neville Kuyt
– Neville Kuyt, Commented Nov 25, 2011 at 9:53

MatTheCat · Accepted Answer · 2011-11-25 10:05:43Z

5

What about unique index?

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

EDIT:

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

edited Nov 25, 2011 at 10:05

answered Nov 25, 2011 at 9:54

MatTheCat

18.8k6 gold badges59 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lollercoaster Over a year ago

right but it is on two or more rows. I don't want it to fail on either column getting a duplicate value, only when BOTH do

Jaydee Over a year ago

A unique index on the two columns requires that the combination of the two is unique. so "foo,foo" , "foo,bar" , "bar,foo" and "bar,bar" would all co-exist.

勿绮语 · Accepted Answer · 2011-11-25 09:55:25Z

2

In this case, create a unique index on (bar, foo), so that the insert fails on duplicated value. You just need to handle the exception in php. This way, the code is cleaner and faster.

answered Nov 25, 2011 at 9:55

勿绮语

9,3401 gold badge31 silver badges37 bronze badges

2 Comments

lollercoaster Over a year ago

right but it is on two or more rows. I don't want it to fail on either column getting a duplicate value, only when BOTH do

勿绮语 Over a year ago

That's exactly the case. If the unique index is on column set (bar, foo), then the insert fails if the combination of values (bar value, foo value) is a duplicate, but not when bar alone or foo alone is a duplicate.

Monkie · Accepted Answer · 2011-11-25 10:29:15Z

Just use a UNIQUE key on the columns and the INSERT IGNORE statement to insert new rows (duplicate rows are IGNORED).

Beware that the UNIQUE key may not exceed a 1000 bytes, meaning that the potential number of bytes contained in the fields foo and bar together may not exceed a 1000 bytes. If this creates a problem, just MD5 the CONCATENATED values into its own column at insert time, like (in PHP) md5($foo.$bar), and set the unique key to that column.

CREATE TABLE  `test_unique` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `foo` varchar(45) default NULL,
  `bar` varchar(45) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE INDEX `Unique` (`foo`,`bar`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT IGNORE INTO `test_unique` VALUES
(1, 'foo1', 'bar1'),
(2, 'foo2', 'bar2');

INSERT IGNORE INTO `test_unique` VALUES
(2, 'foo2', 'bar2');

Collectives™ on Stack Overflow

avoiding duplicates in MySQL on mulitple column values

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related