我们应该在数据库中做多少工作？
我们应该在数据库中做多少工作？ 好吧，我真的很困惑到底应该在数据库中完成多少“工作”，以及必须在应用程序级别完成多少工作？
我的意思是，我不是在谈论明显的东西，比如我们应该在应用程序级别而不是数据库级别将字符串转换为 SHA2 哈希值。
而是更模糊的东西，包括但不限于“我们应该检索数据吗？” 4 列并在应用程序级别执行大写/串联，或者我们应该在数据库级别执行这些操作并将计算结果发送到应用程序级别？
如果您可以列出更多其他示例，那就太好了。
how much work should we do in the database?
Ok I'm really confused as to exactly how much "work" should be done IN the database, and how much work had to be done instead at the application level?
I mean I'm not talking about obvious stuff like we should convert strings into SHA2 hashes at the application level instead of the database level..
But rather stuff that are more blur, including, but not limited to "should we retrieve the data for 4 column and do a uppercase/concatenation at the application level, or should we do those stuff at the database level and send the calculated result to the application level?
And if you could list any more other examples it would be great.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这实际上取决于您的需要。
我喜欢在数据库中完成我的业务逻辑，其他人强烈反对。
您可以在 SQL 中使用触发器和存储过程/函数。
MySQL 链接：
http://dev.mysql.com/doc/refman/5.5/en /triggers.html
http://www.mysqltutorial.org/introduction-to-sql-stored -procedures.aspx
http://dev.mysql.com/doc/refman/5.5 /en/stored-routines.html
我在触发器和存储过程中执行业务逻辑的原因
请注意，我不是在谈论将数据库结构转向业务逻辑，而是谈论将业务逻辑放入触发器和存储过程。
有些人强烈反对这一点，但这种方法对我来说效果很好，并且大大简化了我的应用程序的调试和维护。
It really depends on what you need.
I like to do my business logic in the database, other people are religously against that.
You can use triggers and stored procedures/functions in SQL.
Links for MySQL:
http://dev.mysql.com/doc/refman/5.5/en/triggers.html
http://www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspx
http://dev.mysql.com/doc/refman/5.5/en/stored-routines.html
My reasons for doing business logic in triggers and stored proces
Note that I'm not talking about bending the database structure towards the business logic, I'm talking about putting the business logic in triggers and stored procedures.
Some people disagree strongly with this, but this approach works well for me and has simplified debugging and maintenance of my applications a lot.
一般来说，最好只期望数据库中的“
数据”。由应用程序来应用业务/域逻辑并理解检索到的数据。强烈建议在应用层执行以下操作：1）格式化日期
2）应用数学函数，例如插值/外推等
3) 动态排序（基于列）
然而，有时情况下不需要在数据库级别做一些事情。
Generally, its a good practice to expect only "
Data" from the Database. Its upto Application(s), to apply Business/Domain Logic and make sense of the data retrieved. Its highly recommended to do the following things in the Application Layer:1) Formatting Date
2) Applying Math functions, such as interpolation/extrapolation, etc
3) Dynamic sorting (based on columns)
However, situations sometime warrant few things to be done at the database level.
在我看来，应用程序应该使用数据，数据库应该提供它们，并且应该明确关注点分离。因此，数据库根据请求的条件对记录进行排序、排序和过滤，但由应用程序对这些记录应用一些业务逻辑并将它们“转换”为对用户有意义的内容。
例如，在我之前的公司中，我们致力于工作时间计算的大型应用程序。此类应用程序的一个明显功能是跟踪员工的假期天数 - 员工每年有多少天、他使用了多少天、还剩下多少天等。基本上，我们可以编写一些触发器和过程来自动更新这些列。因此，当员工的假期得到批准时，他申请的天数将从他的“假期池”中取出并添加到“已使用的假期”中。非常简单的事情，但我们决定在应用程序级别上明确它，天哪，很快我们就很高兴我们这样做了。申请必须符合劳动法，但很快就发现，并不是所有员工的假期都是平等计算的，有时假期可能根本不是假期，但这不是重点。如果我们将这个“简单”的操作放入数据库中，我们就必须对数据库进行版本控制，并对假期相关逻辑进行每一个微小的更改，这将导致我们在客户支持领域直接陷入困境，因为事实上可以只更新应用程序无需更新数据库（当然，除了改变数据库结构的明显“突破”时刻）。
In my opinion application should use data and database should provide them and that should be clear separation of concerns. So database gives records sorted, ordered and filtered according to requested conditions but it is up to application to apply some business logic to that records and "convert" them into something meaningful to the user.
For example, in my previous company we worked on big application for work time calculations. One of obvious functionalities in this kind of application is tracking vacation days of employees - how many days employee has per year, how many he used, how many left, etc. Basically we could write some triggers and procedures that would update those columns automatically. So when employee had his vacation days approved amount of days he applied for is taken from his "vacation pool" and added to "vacation days used". Pretty easy stuff but we decided to make it explicit on application level and boy, very soon we were happy we did it that way. Application had to be labor law compliant and it quickly turned out that not for all employees vacation days are calculated equally and sometimes vacation day can be not so vacation day at all but that is beside the point. Had we put this "easy" operation in database we had to version our database with every little change to a vacation days related logic and that would lead us straight to hell in customer support field due to a fact that it was possible to update only application without a need to update database (except clear "breakthrough" moments where database structure was changed of course).
根据我的经验，我发现许多应用程序都是从一组简单的表开始，然后是一些存储过程来提供基本功能。这非常有效；它通常会产生高性能并且易于理解，并且还减轻了对复杂中间层的任何需求。
然而，应用程序不断增长。具有数千个存储过程的大型数据驱动应用程序并不罕见。将触发器加入其中，您就拥有了一个应用程序，对于原始开发人员（如果他们仍在开发）之外的任何人来说，该应用程序都很难维护。
我将介绍一下将大部分逻辑放在数据库中的应用程序 - 当您拥有一些优秀的数据库开发人员和/或您拥有无法更改的遗留模式时，它们可以很好地工作。我这样说的原因是，当您让 ORM 控制架构时，ORM 可以减轻应用程序开发这一部分的痛苦（如果没有，您通常需要做大量的调整才能使其正常工作）。
如果我正在设计一个新的应用程序，那么我通常会选择由我的应用程序域决定的模式（其设计将在代码中）。我通常会让 ORM 处理对象和数据库之间的映射。当涉及到数据访问时，我会将存储过程视为规则的例外（存储过程中的报告比试图哄骗 ORM 有效地生成复杂的输出要容易得多）。
但要记住的最重要的事情是，在设计方面不存在“最佳实践”。作为开发人员，您可以根据自己的设计情况权衡每个选项的利弊。
In my experience I've found that many applications start with a straight-forward set of tables and then and handful of stored procedures to provide basic functionality. This works very well; it usually yields high performance and is simple to understand, it also mitigates any need for a complex middle-tier.
However, applications grow. It's not unusual to see large data-driven applications with thousands of stored procedures. Throw triggers into the mix and you have an application which, for anybody other than the original developers (if they're still working on it), is very difficult to maintain.
I will put a word in for applications which place most logic in the database - they can work well when you have some good database developers and/or you have a legacy schema which cannot be changed. The reason I say this is that ORMs take much of the pain out of this part of application development when you let them control the schema (if not, you often need to do a lot of fiddling to get it working).
If I was designing a new application then I would usually opt for a schema which is dictated by my application domain (the design of which will be in code). I would normally let an ORM handle the mapping between the objects and the database. I would treat stored procedures as exceptions to the rule when it came to data access (reporting can be much easier in sprocs than trying to coax an ORM into producing a complex output efficiently).
The most important thing to remember though, is that there are no "best practices" when it comes to design. It is up to you the developer to weigh up the pros and cons of each option in the context of your design.