在MySQL中更新关联表 [英] Updating an associative table in MySQL

问题描述

下面是我的(简化的)模式(在MySQL版本5.0.51b中)和更新策略.必须有一个更好的方法.插入新项目需要四次访问数据库，而编辑/更新项目最多需要 7 ！

Below is my (simplified) schema (in MySQL ver. 5.0.51b) and my strategy for updating it. There has got to be a better way. Inserting a new item requires 4 trips to the database and editing/updating an item takes up to 7!

项目:itemId，itemName
类别:catId，catName
地图:mapId *，itemId，catId
* mapId(varchar)是itemId +的连载.+ catId

items: itemId, itemName
categories: catId, catName
map: mapId*, itemId, catId
* mapId (varchar) is concat of itemId + | + catId

1)如果要插入:插入项目.通过MySQL API获取itemId.
其他更新:只需更新项目表.我们已经有了itemId.

1) If inserting: insert item. Get itemId via MySQL API.
Else updating: just update the item table. We already have the itemId.

2)有条件地批量插入类别.

2) Conditionally batch insert into categories.

INSERT IGNORE INTO categories (catName)
VALUES ('each'), ('category'), ('name');

3)从类别中选择ID.

SELECT catId FROM categories
WHERE catName = 'each' OR catName = 'category' OR catName = 'name';

4)有条件地批量插入 map .

4) Conditionally batch insert into map.

INSERT IGNORE INTO map (mapId, itemId, catId)
VALUES ('1|1', 1, 1), ('1|2', 1, 2), ('1|3', 1, 3);

如果要插入:我们完成了.其他更新:继续.

If inserting: we're done. Else updating: continue.

5)可能我们不再将类别与更新之前进行的此项相关联.删除此itemId的旧类别.

5) It's possible that we no longer associate a category with this item that we did prior to the update. Delete old categories for this itemId.

DELETE FROM MAP WHERE itemId = 2
AND catID <> 2 AND catID <> 3 AND catID <> 5;

6)如果我们已将自己与某个类别分离，则有可能我们将其孤立了.我们不希望没有项目的类别.因此，如果受影响的行>0 ，杀死孤立的类别.我还没有找到在MySQL中组合这些方法的方法，因此这是#6&#7.

6) If we have disassociated ourselves from a category, it's possible that we left it orphaned. We do not want categories with no items. Therefore, if affected rows > 0, kill orphaned categories. I haven't found a way to combine these in MySQL, so this is #6 & #7.

SELECT categories.catId
FROM categories
LEFT JOIN map USING (catId)
GROUP BY categories.catId
HAVING COUNT(map.catId) < 1;

7)删除在步骤6中找到的ID.

7) Delete IDs found in step 6.

DELETE FROM categories
WHERE catId = 9
  AND catId = 10;

请告诉我，还有一种我看不到的更好的方法.

Please tell me there's a better way that I'm not seeing.

推荐答案

步骤6&7个可以很容易地组合:

Steps 6 & 7 can be combined easily enough:

DELETE categories.*
FROM categories
LEFT JOIN map USING (catId)
WHERE map.catID IS NULL;

步骤3&4个也可以组合:

Steps 3 & 4 can also be combined:

INSERT IGNORE INTO map (mapId, itemId, catId)
    SELECT CONCAT('1|', c.catId), 1, c.catID
    FROM categories AS c
    WHERE c.catName IN('each','category','name');

否则，您的解决方案是非常标准的，除非您想使用触发器来维护映射表.

Otherwise, your solution is pretty standard, unless you want to use triggers to maintain the map table.

这篇关于在MySQL中更新关联表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！