基于现有行的受控表查询

Question

I have a product_group table with the following fields: group_id, product_id, order. The table will be queried against a lot: a single-form view will make it possible to insert new records and/or update existing ones with one submit.

I'm trying to figure out optimal solution to cover the following 3 cases:

1. User tries to insert an existing row: do nothing. Here a unique index of the 3 columns can be useful.

2. User changes only the order column: perform an update.

3. User inserts a completely new set of values: perform an insert.

Is there a way to put all of this together in one MySQL query? If not, what would be the best approach here? The goal is to limit database queries as much as possible.

Answer 1

Does this do what you want?

insert into product_group(group_id, product_id, `order`)
    values (@group_id, @product_id, @order)
    on duplicate key update `order` = values(`order`);

Along with a unique index on group_id, product_id:

create unique index idx_product_group_2 on product_group(group_id, product_id)

This handles your three cases:

1. Because the value assignment is a no-op if the values are the same.
2. The order column will be updated if the other two have the same value.
3. A new row that has a different group_id or product_id will be inserted.

As a note, order is a lousy name for a column, because it is a SQL key word.