mysql - 更新，插入或删除行值列表

Question

My situation is for tracking order ids. I have a separate table which is linked to a main account through account_id.

In my account management I allow the order id's to be added, edited, deleted. For example, in my form I would show the order ID's as order1,order2,order3,order4. While this shows them in a comma sep string each has its own row in the accounts_orders table.

What logic should be used here as far as editing and updating the database? All I can think of is a comparison between my new list and the current list in the database.

If in new list, but not in current... add it. If not in new list, but in current... delete it.

Is there any easier way to go about this? I do not want to delete all records and insert new ones... as that would increase my primary key much more than necessary.

I am not looking for a code solution, but more of the logic as it seems there must be an easier way to update/insert/delete the new list of values with that which is in the database so they are equal.

accounts_orders
---------------  
order_id (primary auto inc)
account_id (foreign key)
order_ref (unique)

Answer 1

Consider the following...

CREATE TABLE account_orders
(account_id INT NOT NULL,order_id INT NOT NULL,PRIMARY KEY(account_id,order_id));

INSERT INTO account_orders VALUES
(1,101),(1,102),(1,103);

Now, you can add, edit or delete/dis-associate accounts and orders