MySQL 5 Stored Procedures: Relic or Revolution?
Although all relational databases implement a common set of SQL syntax, each RDBMS offers proprietary extensions to this standard SQL, and MySQL is no exception. If you are attempting to write an application that is designed to be independent of the underlying database, you probably will want to avoid these extensions in your application. However, sometimes you'll need to use specific syntax to get the most out of the server. For instance, in MySQL, you often will want to employ MySQL hints, execute non-ANSI statements, such as LOCK TABLES, or use the REPLACE statement.
Using stored programs can help you avoid RDBMS-dependent code in your application layer while allowing you to continue to take advantage of RDBMS-specific optimizations. In theory, stored program calls against different databases can be made to look and behave identically from the application's perspective. You can encapsulate all the database-dependent code inside the stored procedures. Of course, the underlying stored program code will need to be rewritten for each RDBMS, but at least your application code will be relatively portable.
However, there are differences between the various database servers in how they handle stored procedure calls, especially if those calls return result sets. MySQL, SQL Server and DB2 stored procedures behave very similarly from the application's point of view. However, Oracle and Postgres calls can look and act differently, especially if your stored procedure call returns one or more result sets.
So, although using stored procedures can improve the portability of your application while still allowing you to exploit vendor-specific syntax, they don't make your application totally portable.
MySQL stored programs can be used for a variety of tasks in addition to traditional application logic:
Triggers are stored programs that fire when data modification language (DML) statements execute. Triggers can automate denormalization and enforce business rules without requiring application code changes and will take effect for all applications that access the database, including ad hoc SQL.
The MySQL event scheduler introduced in the 5.1 release allows stored procedure code to be executed at regular intervals. This is handy for running regular application maintenance tasks, such as purging and archiving.
The MySQL stored program language can be used to create functions that can be called from standard SQL. This allows you to encapsulate complex application calculations in a function and then use that function within SQL calls. This can centralize logic, improve maintainability and, if used carefully, improve performance.
The bottom line is that MySQL stored procedures give you more options for implementing your application and, therefore, are undeniably a “good thing”. Judicious use of stored procedures can result in a more secure, higher performing and maintainable application. However, the degree to which an application might benefit from stored procedures is greatly dependent on the nature of that application. I hope this article helps you make a decision that works for your situation.
- Readers' Choice Awards 2013
- Linux Kernel News - November 2013
- December 2013 Issue of Linux Journal: Readers' Choice
- Mars Needs Women
- RSS Feeds
- Raspberry Pi: the Perfect Home Server
- Sublime Text: One Editor to Rule Them All?
- Advanced Hard Drive Caching Techniques
- Web Administration Scripts
- IBM Will Minimize Impact of Future Disasters
- thanks for share, great
7 hours 30 min ago
- There are factors which are
12 hours 29 min ago
- Gnome 3 ?
13 hours 14 min ago
- Reply to comment | Linux Journal
17 hours 22 min ago
- "Redis RethinkDB 4.5%" on Best NoSQL Databases
1 day 3 hours ago
- on the ground
1 day 9 hours ago
- I was able to read the whole
1 day 11 hours ago
- since i have read the title i
1 day 14 hours ago
- Belanja Online Cari Voucher Diskon
1 day 14 hours ago
- The kernel doesn't really
2 days 2 hours ago