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?

推荐答案

点/计数器点与 Jeff Atwoods谁需要存储过程，反正？从2004年开始：

1）存储过程是用大型数据库语言写成的，比如PL / SQL 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.

em> ：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 isn一个银弹 - 由于数据库抽象，大多数支持运行本机存储过程/函数，以获得良好的查询。这是很好，但完全击败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）存储过程通常不能在同一个IDE中调试编写你的UI。每次我在procs中隔离一个异常，我必须停止我在做什么，清除我的蟾蜍副本，并加载数据库包以查看是什么问题。经常在两个完全不同的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中失败的特定行返回的cryptic'errors'，例如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）存储过程不能传递对象。所以，如果你不小心，你可以结束一个zillion参数。如果您必须使用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.

响应 ：这是一个好东西你得到模型 - 视图 - 控制器（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屋！