CODEWITHSUNDEEP
mysqlsqlindexingtransactionsddl
lightning_missile
lightning_missile

Reputation: 2992

Excluding unique indexes in insert on duplicate key update

Suppose I have a table:

create table sample(
i int(5) primary key auto_increment,
a int(5) not null unique,
b int(5) not null unique,
c int(5) not null unique);

Also suppose this table can contain millions of records, and many times millions of records can be added or updated. For this task, I plan to use insert on duplicate key update.

Columns a, b, and c are unique columns, but I might have to exchange the column values of b and c across the table. For example:

a b c
1 2 3
2 4 5

I would like to exchange column b's values. I would like to update it to:

a b c
1 4 3
2 2 5

Now, throughout the insert on duplicate key update operation, the value of column a can't change. But columns b and c can.

For this reason, I'd like to just use the index of column a in the insert on duplicate key update, not all index. That is, when insert on duplicate key update decides to update a row, the query should be:

update sample set a=?, b=?c=? where a=?

rather than

update sample set a=?, b=?c=? where a=? or b=? or c=?

\Is it possible?

One way I thought of doing this is to just drop the indexes before executing the query and putting the indexes back again.

alter table sample drop index b;
alter table sample drop index c;
// query
alter table sample add unique b;
alter table sample add unique c;

However, if I'm inside a transaction, this would cause an implicit commit, I would not like that at all.

Is there a better way of doing what I want?

Upvotes: 0

Views: 492

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You seem to want a unique index on the combination of values, not on each individual column (at least, your sample data suggests this).

If so, then you need to define the table as:

create table sample (
    i int(5) primary key auto_increment,
    a int(5) not null,
    b int(5) not null,
    c int(5) not null,
    unique (a, b, c)
);

Upvotes: 1

Related Questions