数据库中的业务逻辑与代码中的业务逻辑？
作为一名软件工程师，我强烈倾向于在应用程序层编写业务逻辑，而通常仅依赖数据库进行 CRUD（创建、检索、更新和删除）操作。另一方面，我运行过应用程序（通常是较旧的应用程序），其中大量业务逻辑是在存储过程中编写的，因此有人更喜欢在数据库层中编写业务逻辑。
对于那些拥有和/或喜欢在存储过程中编写业务逻辑的人，您使用此方法的原因是什么？
As a software engineer, I have a strong bias towards writing business logic in the application layer, while typically relying on the database for little more than CRUD (Create Retrieve Update and Delete) operations. On the other hand, I have run across applications (typically older ones) where a large amount of the business logic was written in stored procedures, so there are people out there that prefer to write business logic in the database layer.
For the people that have and/or enjoy written/writing business logic in a stored procedure, what were/are your reasons for using this method?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（16）
我尝试将数据库中的业务逻辑严格限制为必须执行大量查询和更新才能执行单个应用程序操作的过程。有些人可能会争辩说，即使这样也应该在应用程序中，但如果可以的话，我喜欢降低 IO。
数据库非常适合 CRUD，但如果它们因逻辑而变得臃肿：
I try to seriously limit my business logic in the DB to only procs that have to do alot of querying and updating to perform a single application operation. Some may argue that even that should be in the app, but I like to keep the IO down if I can.
Databases are great for CRUD but if they get bloated with logic:
尽最大可能，将您的业务逻辑保持在最可测试和可调试的环境中。在其他人现有的答案中，有一些将业务逻辑存储在数据库中的正当理由，但它们几乎总是远远超过了这一点。
To the maximum extent possible, keep your business logic in the environment that is the most testable and debuggable. There are some valid reasons for storing business logic in the database in other people's existing answers, but they are almost always far outweighed by this.
将业务逻辑限制在应用程序层充其量是短视的。经验丰富的专业数据库设计者很少允许它出现在他们的系统上。数据库需要有约束、触发器和存储过程来帮助定义来自任何源的数据将如何进入其中。
如果数据库要保持其完整性并确保所有新数据或数据更改的来源都遵循规则，那么数据库就是放置所需逻辑的地方。将其放在应用程序层将是一场即将发生的数据噩梦。数据库不仅仅从一个应用程序获取信息。应用程序中的业务逻辑经常被导入无意中绕过（假设您有一位新客户希望将其旧历史数据导入您的系统或大量目标记录，没有人会通过界面输入一百万个可能的目标，它会在导入中发生。）它也可以通过通过查询窗口进行的更改来绕过，以修复一次性问题（例如将所有产品的价格提高 10%）。如果您有应该应用于数据更改的应用程序层逻辑，则不会。现在放在应用层也可以，发送坏数据到数据库浪费网络带宽没有意义，但不放在数据库迟早会导致数据问题。
将所有这些内容保留在数据库中的另一个原因与用户进行欺诈的可能性有关。如果将所有逻辑放在应用程序层中，则必须授予用户直接访问表的权限。如果将所有逻辑封装在存储过程中，它们可能会被限制为仅执行存储过程允许的操作，而不执行其他操作。我不会考虑允许用户对存储财务记录或个人信息（例如健康记录）的数据库进行任何类型的访问，因为我不会允许除了几个数据库管理员之外的任何人以任何方式直接访问生产记录。欺诈行为比许多开发人员意识到的要多，而且几乎没有人在设计中考虑到这种可能性。
如果您需要导入大量数据，通过数据访问层可能会减慢导入速度，因为它没有利用数据库旨在处理的基于集合的操作。
Limiting the business logic to the application layer is short-sighted at best. Experienced professional database designers rarely allow it on their systems. Database need to have constraints and triggers and stored procs to help define how the data from any source will go into it.
If the database is to maintain its integrity and to ensure that all sources of new data or data changes follow the rules, the database is the place to put the required logic. Putting it the application layer is a data nightmare waiting to happen. Databases do not get information just from one application. Business logic in the application is often unintentionally bypassed by imports (assume you got a new customer who wanted their old historical data imported to your system or a large number of target records, no one is going to enter a million possible targets through the interface, it will happen in an import.) It is also bypassed by changes made through the query window to fix one-time issues (things like increasing the price of all products by 10%). If you have application layer logic that should have been applied to the data change, it won't be. Now it's ok to put it in the application layer as well, no sense sending bad data to the database and wasting network bandwidth, but to fail to put it in the database will sooner or later cause data problems.
Another reason to keep all of this in the database has to to with the possibility of users committing fraud. If you put all your logic in the application layer, then you must grant the users access directly to the tables. If you encapsulate all your logic in stored procs, they can be limited to doing only what the stored procs allow and not anything else. I would not consider allowing any kind of access by users to a database that stores financial records or personal information (such as health records) as I would not allow anyone except a couple of dbas to directly access the production records in any way shape or form. More fraud is committed than many developers realize and almost none of them consider the possibility in their design.
If you need to import large amount of data, going through a data access layer could slow down the import to a crawl becasue it doesn't take advanatge of the set-based operations that databases are designed to handle.
您对“业务逻辑”一词的使用相当模糊。
它可以被解释为包括对数据实施约束（也称为“业务规则”）。这些的执行无疑属于 dbms 的范围。
它也可以被解释为包括“如果有新客户到来，那么我们会在一周内向他发送一封欢迎信”之类的内容。试图将这样的东西推送到数据层可能是一个很大的错误。在这种情况下，“创建新的欢迎信”的驱动程序可能应该是也触发新客户行插入的应用程序。想象一下，每个新的数据库行插入都会触发一封新的欢迎信，然后突然我们接管了另一家公司，我们必须将该公司的客户集成到我们自己的数据库中......哎呀。
Your usage of the term "business logic" is rather vague.
It can be interpreted to mean to include the enforcement of constraints on the data (aka 'business rules'). Enforcement of these unequivocally belongs in the dbms, period.
It can also be interpreted to mean to include things like "if a new customer arrives, then within a week we send him a welcome letter." Trying to push stuff like this in the data layer is probably a big mistake. In such cases, the driver for "create a new welcome letter" should probably be the application that also triggers the new customer row insertion. Imagine every new database row insertion triggering a new welcome letter, and then suddenly we take over another company and we must integrate that company's customers in our own database ... Ouch.
我们在适当的情况下在数据库层进行大量处理。有很多操作您不希望将大型数据集拉回到应用程序层进行分析。对于我们来说，这也是一种更简单的部署——单点部署与在所有安装点更新应用程序相比。但很大程度上取决于您的应用程序及其用途；这里没有一个好的答案。
We do a lot of processing in the DB tier, where appropriate. There's a lot of operations you wouldn't want to pull back large datasets to the app tier to do analysis on. It's also an easier deployment for us -- a single point vs. updating applications at all install points. But a lot depends on your application and what it does; there's no single good answer here.
将业务逻辑放入数据库的两个充分理由是：
针对其他应用程序
可以访问不可以访问的数据库
实现类似的逻辑。
应用层，它减少了
当你搬到新的地方时必要的工作
客户端的技术。
Two good reasons for putting the business logic in the database are:
against additional applications that
may access the database that don't
implement similar logic.
application layer and it reduces the
work necessary when you move to new
technologies on the client side.
有几次我将“逻辑”放入存储过程中，因为 CRUD 可能发生在多个地方。我不得不说，“逻辑”并不是真正的业务逻辑，而是更多的“完整性逻辑”。它可能是相同的 - 如果某些内容以某种方式被删除或更新，则可能需要进行一些清理，并且如果删除或更新可能发生在具有不同代码库的多个工具中，则将其放入进程中是有意义的全部用过。
此外，有时“业务逻辑线”相当模糊。以报告为例，它们可能依赖于存储过程或视图，这些存储过程或视图封装了有关模式对业务意味着什么的“智能”。您是否经常看到 CASE 语句等基于列值或其他标准“执行操作”的语句？可以被解释为业务逻辑，但它可能确实属于可以优化的数据库等。
On a couple of ocassions I have put 'logic' in sprocs because the CRUD might be happening in more than one place. By 'logic' I would have to say it is not really business logic but more 'integrity logic'. It might be the same - some cleanup might be necessary if something gets deleted or updated in a certain way, and if that delete or update could happen from more than one tool with different code-bases it made sense to put it in the proc they all used.
In addition, sometimes the 'business logic line' is pretty blurry. Take reports for example - they may rely on stored procedures or views that encapsulate 'smarts' about what the schema means to the business. How often have you seen CASE statements and the like that 'do things' based on column values or other critieria? Could be construed as business logic and yet it probably does belong in the DB where it can be optimized, etc.
我想说，如果“业务逻辑”意味着应用程序流程、用户控制、定时操作以及一般的“做业务的东西”，那么它应该位于应用程序层。但是，如果这意味着确保无论您如何挖掘数据，它总是有意义并且是一个合理的、非自冲突的整体，那么毫无疑问，执行这些规则的检查会进入数据库。总是有很多方法可以将数据推送到数据库并在数据到达数据库后对其进行操作。并非所有这些方式都内置了“业务逻辑”。例如，您会发现凌晨 3 点的支持电话中通过 DOS 窗口进入数据库的 SQL 会话在其允许的内容方面非常自由！如果数据库中没有逻辑来确保所有数据更改都有意义，那么您可以肯定，随着时间的推移，数据会变得非常非常混乱。由于系统的价值取决于其所保存的数据，因此投资回报率要低得多。
I'd say if 'business-logic' means application flow, user control, timed operations and generally 'doing-business-stuff' then it should be in the application layer. But if it means making sure that no matter how you dig around in the data, it always makes sense and is a sensible, non-self-conflicting whole, then the checks to enforce those rules go in the DB, absolutely, no questions. There are always many ways to push data into the DB and manipulate it once its there. Not all those ways have 'business-logic' built in to them. You will find a SQL session into a DB through a DOS window on a support call at 3am is very liberal in what it allows for example! If the logic isn't in the DB to make sure that ALL data changes make sense, you can bet for sure that the data will get very, very screwed up over time. And since a system is only as valuable as the data it holds, that makes for a much lower return on investment.
您经常在数据库层找到业务逻辑，因为它通常可以更快地进行更改和部署。我认为通常最好的意图不是把逻辑放在那里，而是因为易于部署，所以它最终放在那里。
You often find business logic at the database layer because it can often be faster to make a change and deploy. I think often the best intentions are not to put the logic there but because of the ease of deployment it ends up there.
我的偏好是将任何复杂的业务逻辑保留在数据库之外，只是为了维护目的。如果我在凌晨 2 点接到电话，我宁愿调试应用程序代码，也不愿尝试单步执行数据库脚本。
My preference is to keep any complicated business logic out of the database, simply for maintenance purposes. If I get a call at 2 o'clock in the morning I would rather debug my application code than try to step through database scripts.
过去我将 BL 放入存储过程的主要原因是数据库中的事务更容易。
如果部署对于您的应用程序来说很困难并且您没有应用程序服务器，则更改存储过程中的 BL 是部署更改的最有效方法。
The primary reason I would put BL in stored procs in the past is that transactions were easier in the database.
If deployments are difficult for your app and you don't have an app-server, changing the BL in stored procedures is the most effective way to deploy a change.
我在一家金融类型的公司工作，该公司的某些规则由各州执行，这些规则及其计算几乎每天（如果不是每周）都会发生变化。既然如此，将处理计算的部分逻辑移至数据库更有意义；可以测试和应用更改，而无需重新编译和重新分发应用程序，而这在不中断业务的情况下是不可能每天完成的。存储过程经过测试、批准和应用，最终用户却一无所知。
随着转向基于 Web 的应用程序，对将逻辑移至数据库的依赖减少，但仍然存在。即使是网络应用程序（取决于语言）也必须编译并发布到网站，这可能会导致停机。
I work for a financial type company where certain rules are applied by states, and these rules and their calculations are subject to change almost daily if not surely weekly. That being the case, it made more sense to move parts of the logic dealing with calculations to the database; where a change can be tested and applied without having to recompile and redistibute an application, which is impossible to do daily without disrupting business. The stored proc is tested, approved, applied and the end user is none the wiser.
With the move to web based applications, the reliance on moving the logic to the database is less but still present. Even web apps (depending on the language) must be compiled and published to the site which could cause downtime.
有时业务逻辑太慢而无法在应用程序层上运行。在客户端功率和带宽更加有限的旧系统上尤其如此。
Sometimes business logic is too slow to run on the app layer. This is especially true on on older systems where client power and bandwidth was more limited.
使用数据库完成工作的主要原因是您拥有单点控制。通常，应用程序开发人员会在应用程序的不同部分重复使用或重写代码片段。即使假设这些都以完全相同的方式工作（这是值得怀疑的），当业务逻辑发生变化时，应用程序需要进行审查、重新编码、重新编译。除非参数发生变化，否则在业务逻辑仅存储在数据库中的情况下没有必要这样做。
The main reason for using the database to do the work is that you have a single point of control. Often, app developers re-use or rewrite code fragments in different parts of the application. Even assuming that these all work exactly the same way (which is doubtful), when the business logic changes, the app needs to be reviewed, recoded, recompiled. Unless the parameters change, this would not be necessary where the business logic is stored only in the database.
我所在的团队负责建立和维护一个相当大的财务系统，我发现没有办法将逻辑放入应用程序层中，以执行影响或受到数十万条记录约束的操作。
除了性能问题之外，如果发生错误，纠正存储过程比调试应用程序、修复、重新编译、重新部署代码要快得多，停机时间更长
I'm in a team to build-up and maintain a rather large financial system, and I find no way put the logic into the application layer for action that affect to or get constraints from dozens of thousand records.
Beside the performance issue, should errors happen, rectifying a stored procedures is much faster than debugging the application, fixing, recompiling, redeploying the code with longer downtime
我认为特别是对于我正在开发的旧应用程序（银行），其中业务逻辑非常庞大，几乎不可能在应用程序层中执行所有这些业务逻辑，而且当我们将这些逻辑放在应用程序层中时，性能也会受到很大影响数据库的获取次数越多，就会导致更多的资源利用率（如果在 java 层完成，则会导致更多的 java 对象）和网络问题，并忘记 abt 性能。
I think Specially for older applications which i working on (Banking) where the Bussiness logic is huge, it's almost next to impossible to perform all these business logic in application layer, and also It's a big performenance hit when we put these logic in Application layer where the number of fetch to the database is more, results in more resource utilization(more java objects if it's done in java layer) and network issues and forget abt performenance.