我有一个问题，我在两个表之间有一个 M:M 关系，一个连接表将它们的每个 ID 存储为它们各自表中主键的外键。

例如:

 CREATE TABLE todo (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    title TEXT NOT NULL,
    slug TEXT NOT NULL,

    description VARCHAR(100) NOT NULL,

    user_id INT(10) UNSIGNED NOT NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE category (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    name TEXT,
    slug TEXT,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE todo_category (
    todo_id INT(10) UNSIGNED NOT NULL,
    category_id INT(10) UNSIGNED NOT NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,

    PRIMARY KEY (`todo_id`, `category_id`),

    CONSTRAINT `f_todo_category_todo` FOREIGN KEY (`todo_id`) REFERENCES `todo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `f_todo_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

我可以创建一个有很多类别的待办事项。我还可以选择那个待办事项，或所有待办事项及其各自的类别——而且我只需 1-2 个查询就可以相当干净地完成这两项任务。

当我尝试UPDATE todo 时，我还可以使用 todo_category< 上的="">REPLACE INTO 查询来更新类别表。我知道它会删除并再次创建这些行，但我认为在这种情况下没问题，因为通常不会有超过一手的类别。然而，这种方法的问题在于，如果没有添加而是删除了新类别怎么办？这让我想到了我的问题......

是否有一个标准的查询来做这样的事情(更新 todo_category 表以删除不存在的 id，并添加新的 id——在一个查询中)，或者这是在哪里我必须只使用代码逻辑并为我自己的案例场景执行多个查询，例如我下面的示例？

我目前正在尝试这样做的方式如下(我认为这真的很糟糕，因为它需要很多查询和很多行代码):

1. 更新待办事项
2. SELECT 我们刚刚更新的 todo 以便稍后使用它的 ID
3. SELECT 所有与我们从 #2 获得的 ID 对应的类别(我们从这些结果中创建一个已经存在的类别 ID 数组)
4. SELECT 所有类别均具有来自 #2 的匹配 todo_id，以及来自请求中发送的 ID 的匹配类别 ID(这些 ID 由用于更新此待办事项及其类别的表单)
5. 对来自 #3 的类别 ID 和来自 #4 的类别 ID 进行比较，看看有什么是新的，什么不再存在(删除)，并根据这些结果创建两个新数组
6. 执行INSERT 查询以在todo_category 表中添加新类别以反射(reflect)新ID
7. 执行DELETE 查询以删除与todo 当前拥有的类别相比不再出现在请求中的类别

如您所见，它非常细化并且需要很多查询，并且使得该 Todo 模型代码中的 Update 方法比所有其他方法都大得多，并且它使得我觉得有更好的方法。

编辑

这是我的代码，虽然不完整(仅供引用，但我认为这并不重要，因为我在上面解释了我在做什么):

// Update makes changes to an existing item.
func Update(db Connection, title string, description string, slug string, idsFromRequest category.IDList, userID string) (sql.Result, error) {
    // Update the todo
    newSlug := helper.StringToSlug(title, true)
    result, err := db.Exec(fmt.Sprintf(`
        UPDATE %v
        SET title = ?,
            slug = ?,
            description = ?
        WHERE slug = ?
            AND user_id = ?;
    `, table), title, newSlug, description, slug, userID)
    if err != nil {
        return result, err
    }

    // Get the todo to reference its ID
    t, _, err := BySlug(db, newSlug)
    if err != nil {
        return result, err
    }

    // Get the categories the todo currently has
    cats, _, err := category.ByTodoID(db, t.ID)
    if err != nil {
        return result, err
    }

    // Get the ids from all of the categories
    var idsAlreadyExisting []string
    for _, cat := range cats {
        idsAlreadyExisting = append(idsAlreadyExisting, cat.ID)
    }

    // Get the ids to be added (don't yet exist) and the ids to be deleted (not present in the request)
    idsToBeAdded, idsToBeDeleted := diff(idsAlreadyExisting, idsFromRequest)

    // Create the query for adding the category ids for the todo in todo_category
    query := `INSERT INTO todo_category (todo_id, category_id)`
    for i, id := range idsToBeAdded {
        if i == 0 {
            query += ` VALUES`
        }
        query += fmt.Sprintf(` (%s, %s)`, fmt.Sprint(t.ID), id)
        if i == len(idsToBeAdded) - 1 {
            query += ";"
        } else {
            query += ","
        }
    }
    result, err = db.Exec(query)

    // Delete query goes here...

    return result, err
}

最佳答案

如果您在 UPDATE 语句中使用主键而不是使用 slug 和 user_id 会容易得多

待办事项 ID 可以在表单提交时发送。由于所有类别 ID:s 也可以在表单提交时使用，我可能会这样做:

1. UPDATE 表 todo 使用主键而不是 slug 和 user_id
2. INSERT 表 category 中的新类别与表单中提交的类别 ID 数组相比尚不存在
3. 删除 表 todo_category 中的所有行 WHERE todo-ID = 提交的 todo-ID
4. INSERT 表 todo_category 中的行，其中包含从表单提交的所有类别 ID，以及 todo-ID

如果您需要清理和删除表 categories 中与任何 todo-ID 没有任何关系的类别，您可以使用删除查询来完成孤儿类别。

关于MySQL:有没有一种方法可以更新多对多连接表而不会变得非常细化？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/42462503/