不在关联表中创建重复行的最有效方法？
The project I'm working on currently has a categories table, as well as a business_category table (an association between a business and its categories). I want to run a query that will update the values in business_category with the new associations, and keep the ones that exist currently.
I know I can do a DELETE statement to first clear the associations, then do an insert. My question is, is there a better way to do it? Is this performance-savvy? I imagine this query might be called somewhat often, and it seems a bit extreme to delete and re-insert every time it's ran, when really all I want to do is insert if the record doesn't exist already.
For table structure, business_category is just two columns: business_id, and category_id.
Anybody got any ideas? Should I just go ahead and do the delete? Or is there a better way?
Thanks in advance.
我正在处理的项目目前有一个类别表，以及一个business_category表（一个关联 商业及其类别）。 我想运行一个查询，它将使用新关联更新business_category中的值，并保留当前存在的值。 p>
我知道我可以执行 对于表结构， 有人有任何想法吗？ 我应该继续进行删除吗？ 或者有更好的方法吗？ p>
提前致谢。 p>
div> DELETE code >声明首先清除关联，然后进行插入。 我的问题是，有更好的方法吗？ 这是精通表现吗？ 我想这个查询可能会经常被调用，每次运行时删除和重新插入似乎有点极端，如果记录不存在，我真正想做的就是插入。 p> \ n
business_category code>只有两列： business_id code>和 category_id code>。 p>
There are two options I see, both of which should be more performant that always deleting everything and then inserting the updated data.
1) First select all category_id's for the business_id being updated.
2) From the list determine which category_id's need to be removed, and only delete those.
3) From the list determine which category_id's need to be added, and only add those.
4) Anything that is left is the same, so it doesn't need to be touched.
Or you can:
1) Run an INSERT query with a "ON DUPLICATE KEY UPDATE category_id=category_id" (Here's some docs about it)
2) Run a delete query where any rows for that business_id, and where the category_id's are NOT in the list of updated category_id's. This will remove any existing ones that are not in the new updated list. ("DELETE .. WHERE category_id NOT IN ($list_of_categories)")
In the end you basically want to reduce how much writing you have to do because each time you write the index on the table will need to be updated. Doing a large amount of writes will be slower than doing a read and only writing what you have to.
Hope that helps