mysql - 如果唯一约束则使用插入，但如果重复则使用更新

Question

我有一张名为库存的表
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 |
+--------------+----------+---------+------------+----------+

我有第二个表，称为设备

+----------+----------+-----------+-------------+----------+---------+--------------+----------------+-------------+------------+
| 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 |
+----------+----------+-----------+-------------+----------+---------+--------------+----------------+-------------+------------+

我想要的是:
当我
插入库存(chara_id,item_id,item_level,item_qty)值(1,2,2,1)
这将在我的表中插入另一个条目

否则当我插入时:
插入库存(chara_id,item_id,item_level,item_qty)值(1,2,0,1)
它将更新 item_qty = item_qty + 1，这是我上面的库存表中的第一行。

kudos Guyz

Answer 1

如果 chara_id、item_id 和 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 演示