在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.
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屋!