MySQL 存储过程还是 php 代码? [英] MySQL stored procedures or php code?

问题描述

一个一般性的问题，没有考虑到特定的情况 - 是否通常更喜欢使用 MySQL 存储过程而不是编写执行相同计算和查询的 PHP 脚本?

A general question, without a specific case in mind - is it usually preferred to use MySQL stored procedures over writing a PHP script that performs the same calculations and queries?

每种方法有什么好处?

推荐答案

Point/Counter Point with Jeff Atwoods 无论如何，谁需要存储过程?从 2004 年开始:

Point/Counter Point with Jeff Atwoods "Who Needs Stored Procedures, Anyways?" from 2004:

1) 存储过程是用像 PL/SQL (Oracle) 或 T-SQL (Microsoft) 这样的大型数据库语言"编写的.这些所谓的语言是过时的，充满了疯狂、不连贯的设计选择，这些选择总是源于十年向后兼容的痛苦演变.您真的不想在这些东西中编写大量代码.就上下文而言，JavaScript 是 PL/SQL 或 T-SQL 的一大进步.

1) Stored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don't want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from PL/SQL or T-SQL.

响应:SQL"中的S"表示结构化"，而不是标准化"——PLSQL 和 TSQL 都是自定义的扩展 SQL，这也使 ANSI SQL 发挥作用，因为与数据库无关的 SQL 非常少.通常，如果您想要一个执行良好的查询，您不能依赖 ANSI SQL.

Response: The "S" in "SQL" means "Structured", not "Standardized" - PLSQL and TSQL are both custom extensions of SQL, which also bring ANSI SQL into play because there is very little SQL that is database agnostic. Generally, if you want a query that performs well you can't rely on ANSI SQL.

ORM 不是灵丹妙药 - 由于数据库抽象，大多数支持运行本机存储过程/函数以获得性能良好的查询.这很好，但完全违背了 ORM 的目的......

ORM isn't a silver bullet - because of the database abstraction, most support running native stored procedures/functions in order to get a well performing query. Which is nice, but utterly defeats the purpose of ORM...

我永远无法理解为什么 Web 开发、无数技术(HTML、Javascript/AJAX、Flash...)的拼凑总是将 SQL 隔离为家庭中的害群之马.像所有其他人一样，你必须学习它才能从中得到一些东西.一定是您在使用其他技术时获得的即时满足...

I'll never understand why web development, a cobbling of countless technologies (HTML, Javascript/AJAX, Flash...) always segregates SQL as the black sheep of the family. Like all the others, you have to learn it to get something out of it. Must be the instant gratification you get when using the other technologies...

2) 通常无法在您编写 UI 的同一个 IDE 中调试存储过程.每次我在 procs 中隔离异常时，我都必须停止我正在做的事情，破坏我的 Toad 副本，并加载数据库包以查看出了什么问题.经常在两个完全不同、界面和语言完全不同的 IDE 之间进行转换，效率并不高.

2) Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what's going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.

响应:Eclipse 或 Visual Studio 中最初是否有 Javascript 调试器?不，他们允许插件以便将产品推出市场振兴以前不存在的市场.大多数人在 Visual Studio/Eclipse 之外使用 Firebug 都没有问题，为什么 SQL 调试会有所不同?

Response: Was there originally a Javascript debugger in Eclipse or Visual Studio? No, they allow plugins in order to get the product out the door & invigorate a previously non-existent market. Most don't have a problem using Firebug outside of Visual Studio/Eclipse, why should SQL debugging be any different?

3) 当出现问题时，存储过程不会提供太多反馈.除非 proc 是用奇怪的 T-SQL 或 PL/SQL 异常处理内部编码的，否则我们会根据失败的 proc 内的特定行返回神秘的错误"，例如 Table 没有行.嗯，好吗?

3) Stored Procedures don't provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic 'errors' returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?

回应:您不熟悉并不是语言不好的原因.就像你从来没有用你选择的语言搜索奇怪的错误一样......至少 Oracle &MySQL 为您提供错误参考编号.

Response: Your lack of familiarity does not a poor language make. Like you've never had to google for a weird error in your language of choice... At least Oracle & MySQL give you error reference numbers.

4) 存储过程不能传递对象.因此，如果您不小心，最终可能会得到无数个参数.如果您必须使用 proc 用 20 多个字段填充表行，请向 20 多个参数问好.最糟糕的是，如果我传递了一个错误的参数——太多、不够或错误的数据类型——我会收到一个通用的错误调用"错误.Oracle 无法告诉我哪些参数出错了！所以我必须手动仔细研究 20 多个参数，才能找出哪个是罪魁祸首.

4) Stored Procedures can't pass objects. So, if you're not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic "bad call" error. Oracle can't tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.

回应:SQL 是基于 SET 的，完全不同于过程/OO 编程.类型接近于对象，但在某些时候需要在过程/OO 对象和数据库实体之间建立映射.

Response: SQL is SET based, completely unlike procedural/OO programming. Types are close to objects, but at some point there needs to be a mapping between procedural/OO objects and database entities.

5) 存储过程隐藏业务逻辑.我不知道 proc 在做什么，或者它会返回给我什么样的游标 (DataSet) 或值.我无法查看 proc 的源代码(至少，如果我有适当的访问权限，则不会求助于 #2)来验证它实际上是在做我认为的事情——或者设计者打算做的事情.内联 SQL 可能并不漂亮，但至少我可以在上下文中看到它，以及其他业务逻辑.

5) Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.

响应:这是一件好事(tm) - 这就是您获得模型-视图-控制器 (MVC) 的方式，因此您可以在任何多种语言，而不必每次都复制逻辑，同时处理每种语言的怪癖以复制该逻辑.或者，如果有人直接连接到数据库，数据库允许添加坏数据是否好?旅行回来&在应用程序和数据库之间浪费时间&您的应用程序永远无法收回的资源.

Response: This is a Good Thing(tm) - that's how you get Model-View-Controller (MVC), so you can have a front end in any multitude of languages without having to duplicate the logic every time while dealing with each languages' quirks to replicate that logic. Or is it good that the database allows bad data to be added if someone connects directly to the database? Trips back & forth between the application and the database waste time & resources your application will never recoup.

这篇关于MySQL 存储过程还是 php 代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！