mysql 查询有值则更新,MySQL查询：如果条目重复则插入，否则更新现有行值

i have a table called bag(again):

+--------+----------+---------+----------+

| bag_id | chara_id | item_id | item_qty |

+--------+----------+---------+----------+

| 1 | 1 | 2 | 22 |

| 2 | 1 | 1 | 55 |

| 3 | 3 | 1 | 2 |

| 6 | 3 | 4 | 2 |

| 7 | 4 | 4 | 2 |

| 8 | 5 | 4 | 2 |

| 9 | 6 | 4 | 2 |

| 10 | 1 | 5 | 1 |

| 14 | 1 | 8 | 1 |

| 15 | 1 | 6 | 1 |

| 18 | 1 | 4 | 1 |

| 19 | 1 | 3 | 1 |

| 29 | 8 | 1 | 1 |

| 30 | 8 | 7 | 1 |

| 33 | 6 | 2 | 1 |

+--------+----------+---------+----------+

and i have this SQL Statement:

INSERT INTO bag(bag_id, chara_id, item_id, item_qty)VALUES(NULL, :id, :item_id,1)

after asking how to delete duplicates

wat i want to do next(to further restrict duplicates)is when a user buys an item already existing in his bag it increase the item_qty by 1 instead.

like:

if chara_id = exist and item_id exist

item_qty = item_qty + 1

else

#..normal insert

if i use:

INSERT INTO bag(bag_id, chara_id, item_id)VALUES(NULL, 1, 2)

it should not insert but update the item_qty to 23 because that entry is already existing.

解决方案

MySQL supports INSERT ... ON DUPLICATE KEY UPDATE

but before it will work, you need to have an unique constraint on the table. If you don't have a unique constraint yet, based on your example you are checking on two columns if the values already exists,

ALTER TABLE bag ADD CONSTRAINT tb_unique UNIQUE (chara_id, item_id)

Once it has been implemented, ON DUPLICATE KEY UPDATE will not work.

INSERT INTO bag(chara_id, item_id, item_qty)

VALUES(1, 2, 1)

ON DUPLICATE KEY UPDATE item_qty = item_qty + 1