問題描述
一個一般性的問題，沒有考慮到特定的情況 - 是否通常更喜歡使用 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 代碼?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！