处理主从记录的最佳逻辑
假设我们有这些 SQL 表：
[Articles]
bill int (pkey)
arti int (pkey)
name varchar(50)
[Bills]
bill int (pkey)
fdate date
uid int
假设我们在网格上有一个项目列表，代表一张账单：
--------------------------------------------------------------
Id[ 15] Date [01-01-1980]
User [pepe]
Code Name
----------------------------
1 Something
2 Article name
3 lolololololoolo
4 datadatdatdatdata
5 datadatdatdatdata
--------------------------------------------------------------
因此，我们有一个标题，其中包含 id、用户、日期等。然后，网格填充为项目。
当用户决定时（保存按钮），两个表将同时保存。并且，用户还可以查看之前的账单并对其进行修改。那么，什么是更好的呢？
- 循环项目并进行查询来决定：如果存在则为 INSERT，否则为 UPDATE。
- 删除所有项目（按帐单 ID），然后执行所有插入。
Let's say, we have these SQL tables:
[Articles]
bill int (pkey)
arti int (pkey)
name varchar(50)
[Bills]
bill int (pkey)
fdate date
uid int
Let's suppose we have a list of items on a grid, representing a bill:
--------------------------------------------------------------
Id[ 15] Date [01-01-1980]
User [pepe]
Code Name
----------------------------
1 Something
2 Article name
3 lolololololoolo
4 datadatdatdatdata
5 datadatdatdatdata
--------------------------------------------------------------
So, we have a header with, an id, user, date, etc. And, then, the grid filled with items.
Both tables will be saved at the same time when the user decides (a save button). And, the user can also see a previous bill and modify it. So, what's better?
- Loop the items and make a query to decide: if exists is an INSERT, else, is an UPDATE.
- Delete All the items (by bill id) and then, do all INSERTS.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
既不
将写入包装到事务中的两个表
Neither
Wrap the writes to both tables in a transaction
如果您只有两种选择，则主要取决于您的要求和您想要优化的用例。也就是说，您应该在选项 1 中考虑以下
性能，
删除所有文章将花费更长的时间。
在选项 2 中，向包含 10 篇文章的帐单中不添加任何文章与向不包含文章的帐单中添加 10 篇文章所花费的时间大约相同。
审核
选项 2 很难审核
并发
假设应用程序中没有并发检测
两个用户同时打开账单。每个用户添加五篇文章并点击“保存”。
在选项 1 中，您最终会得到 10 篇文章。
在选项 2 中，您最终会得到 5 个。
我不能说哪个是对的。
事务性能
将文章添加到现有账单时，事务将花费比选项 2 所需的时间更长的时间。这会增加该用例出现死锁的可能性。
在没有事务支持的情况下保存失败这假设您的应用不使用事务。
在选项 1 中，新文章可能会丢失，而应删除的已删除文章却没有
在选项 2 中，所有文章都有可能丢失
If you only have the two choices it will mostly depend on your requirements and the use cases you want to optimize for. That said you should consider the following
Performance
In Option 1 Deleting all of the articles will take longer.
In option 2 Adding no articles to an bill with 10 article will take the same about of time as adding 10 articles to a bill with no articles.
Auditing
Option 2 is very difficult to audit
Concurrency
Assuming no concurrency detection in application
Two users open the bill at the same time. Each user adds adds five articles and hits save.
In option 1 you'll end up with 10 articles.
In option 2 you'll end up with five.
I can't say which is right.
Transaction Performance
When adding articles to existing bills Transactions will take longer than need be in option 2. This increases the likelihood of deadlocks for that use case.
Save failures without transaction support this assumes your app doesn't use transactions.
In Option 1 there's a potential that new articles may be lost and deleted articles that should have been deleted aren't
In Option 2 there's a potential for all articles to be lost
为什么不使用 MySQL 语法 ON DUPLICATE KEY？
http://dev.mysql.com/doc/ refman/5.0/en/insert-on-duplicate.html
Why don't you use the MySQL Syntax ON DUPLICATE KEY?
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html