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.
|Security Hardening with Ansible||Aug 18, 2014|
|Monitoring Android Traffic with Wireshark||Aug 14, 2014|
|IndieBox: for Gamers Who Miss Boxes!||Aug 13, 2014|
|Non-Linux FOSS: a Virtualized Cisco Infrastructure?||Aug 11, 2014|
|Linux Security Threats on the Rise||Aug 08, 2014|
|Android Candy: Oyster—Netflix for Books!||Aug 07, 2014|
- Security Hardening with Ansible
- NSA: Linux Journal is an "extremist forum" and its readers get flagged for extra surveillance
- Monitoring Android Traffic with Wireshark
- Tech Tip: Really Simple HTTP Server with Python
- RSS Feeds
- IndieBox: for Gamers Who Miss Boxes!
- [<Megashare>] Watch Mrs Brown's Boys Movie Online Full Movie HD 2014
- Linux Security Threats on the Rise
- Linux Systems Administrator
- Cooking with Linux - Serious Cool, Sysadmin Style!