我有一个名为inventory的表
chara_id, item_id和item_level是一个唯一约束
+--------------+----------+---------+------------+----------+
| inventory_id | chara_id | item_id | item_level | item_qty |
+--------------+----------+---------+------------+----------+
| 35 | 1 | 2 | 0 | 3 |
| 37 | 1 | 14 | 0 | 1 |
| 46 | 4 | 4 | 0 | 1 |
| 49 | 1 | 3 | 0 | 1 |
| 66 | 16 | 6 | 0 | 1 |
| 67 | 16 | 1 | 0 | 1 |
| 75 | 17 | 3 | 0 | 1 |
| 78 | 15 | 2 | 0 | 1 |
| 86 | 12 | 3 | 0 | 2 |
| 88 | 12 | 1 | 0 | 1 |
+--------------+----------+---------+------------+----------+
,我有第二个表叫做equipment
+----------+----------+-----------+-------------+----------+---------+--------------+----------------+-------------+------------+
| equip_id | chara_id | weapon_id | headgear_id | armor_id | ring_id | weapon_level | headgear_level | armor_level | ring_level |
+----------+----------+-----------+-------------+----------+---------+--------------+----------------+-------------+------------+
| 1 | 1 | 2 | 16 | 18 | 17 | 0 | 0 | 0 | 0 |
| 2 | 2 | 2 | 5 | 6 | 0 | 0 | 0 | 0 | 0 |
| 3 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 4 | 2 | 5 | 6 | 8 | 0 | 0 | 0 | 0 |
| 5 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 7 | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 8 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 9 | 9 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10 | 10 | 12 | 5 | 18 | 0 | 0 | 0 | 0 | 0 |
+----------+----------+-----------+-------------+----------+---------+--------------+----------------+-------------+------------+
我想要的是:
当我insert into inventory(chara_id,item_id,item_level,item_qty)values(1,2,2,1)
这将在我的表中插入另一个条目
否则当我插入:
时insert into inventory(chara_id,item_id,item_level,item_qty)values(1,2,0,1)
它将更新item_qty = item_qty + 1
,这是上面我的库存表中的第一行。
kudos guyz
如果chara_id, item_id and item_level
构成UNIQUE
索引,则像这样使用INSERT INTO ... ON DUPLICATE KEY UPDATE
INSERT INTO inventory(chara_id,item_id,item_level,item_qty)
VALUES(1,2,2,1)
ON DUPLICATE KEY UPDATE item_qty = item_qty + VALUES(item_qty);
INSERT INTO inventory(chara_id,item_id,item_level,item_qty)
VALUES(1,2,0,1)
ON DUPLICATE KEY UPDATE item_qty = item_qty + VALUES(item_qty);
这里是SQLFIddle demo