For example, A and B are one-to-many, A has n B records, and the id is (1, 2, 3, 4). Now it is submitted as (3, 4, 5, 6), and the relationship between A and B needs to be updated. For (3, 4, 5, 6), should I first delete the original association (1, 2, 3, 4) and then insert (3, 4, 5, 6)? Or take out the old data and compare it with the new data before updating?
For example, A and B are one-to-many, A has n B records, and the id is (1, 2, 3, 4). Now it is submitted as (3, 4, 5, 6), and the relationship between A and B needs to be updated. For (3, 4, 5, 6), should I first delete the original association (1, 2, 3, 4) and then insert (3, 4, 5, 6)? Or take out the old data and compare it with the new data before updating?
In the absence of an index, I will use delete
first and then insert
.
But when there is an index, I will use the following method to first update
and then insert
:
Add the is_deleted
field to the relationship table, the default is 0, is_deleted
is used to mark whether the relationship is terminated.
Execute SQL
(for the same relationship, only one record will exist)
<code>> `UPDATE table SET is_deleted = 1 WHERE id1=A;` > `REPLACE INTO table (id1, id2, is_deleted) VALUES('A',3,0), ('A',4,0);` </code>
The code of the comparison method is complicated. Adding data items is a small disaster and difficult to maintain
Delete all and insert all mentioned in your question
Delete a single relationship and add a single relationship to create a separate interface.
Add a relationship operation for each deletion of a relationship and call the corresponding separate interface
Simple and crude, delete first and then insert.
It is recommended to take out the old data for comparison.
Delete first, then insert, and add an archive field at the same time, save it as a string, use special symbols to separate this time from the previous relationship, and append.