MySQL Stored Procedure Programming

Preface

Over the past five years or so, we have seen an explosion in the use of open source software in commercial environments. Linux has almost completely displaced various flavors of Unix as the dominant non-Windows operating system; Apache is by far the most significant web server; Perl and PHP form the foundation for millions of commercial web sites; while JBoss, Hibernate, Spring, and Eclipse are making strong inroads into the Java? and J2EE development and application server markets. Although the world of relational databases continues to be dominated by the commercial players (Oracle, IBM, and Microsoft), the commercial use of open source databases is growing exponentially. MySQL is the dominant open source database management system: it is being used increasingly to build very significant applications based on the LAMP (Linux-Apache-MySQL-PHP/Perl/Python) and LAMJ (Linux-Apache-MySQL-JBoss) open source stacks, and it is, more and more, being deployed wherever a high-performance, reliable, relational database is required.

In the landmark book The Innovators Dilemma,^[*] Clayton Christensen provided the first widely accepted model of how open source and other “disruptive” technologies displace more traditional “sustaining” technologies.

When a disruptive technology—Linux for example—first appears, its capabilities and performance are typically way below what would be acceptable in the mainstream or high-end market. However, the new technology is highly attractive to those whose requirements or budgets preclude the use of the established commercial alternatives. These very low-end markets are typically associated with low profit margins and low revenues, so the established vendors are more than happy to retreat from these markets and give the disruptive technology this first foothold. As both the sustaining/traditional and disruptive/innovative technologies improve their capabilities, the disruptive technology becomes attractive to a wider segment of the mainstream market, while the established technologies tend to “overshoot” the demands of the average—or even high-end—consumer.

For the established vendors, the lower ends of the market are always associated with lower profit margins, and the established vendors make a series of apparently sensible business decisions to successively abandon these markets to the newer disruptive technologies. By the time the disruptive technology is seen as a real threat, the established vendors are unable to compete without cannibalizing the revenues from their established products, and in many cases, they become resigned to losing their market dominance.

Open source in general, and MySQL in particular, shows all the characteristics of the disruptive technology model. Five years ago, the capabilities of MySQL were so far behind the requirements of the majority of business users that the use of MySQL in a business environment was almost unheard of. However, MySQL—being free or extremely low cost^[*]—had a definite appeal for users who were unable to afford a commercial relational database. As with most open source technologies, MySQL has experienced rapid technological development—adding transactions, subqueries, and other features normally associated with expensive commercial offerings. By the release of MySQL 4.0, MySQL was being used in a mission-critical manner by an increasing number of high-profile companies, including Yahoo, Google, and Sabre.

Meanwhile, the commercial database companies have been adding features that, although significant for the very high end of the market, have arguably exceeded the requirements of the majority of database users: they are more concerned with performance, manageability, and stability than with advanced features such as composite object data types, embedded Java Virtual Machines, or complex partitioning and clustering capabilities.

With the 5.0 release, MySQL has arguably crossed one of the last remaining capability thresholds for enterprise credibility. The ability to create stored procedures, functions, triggers, and updateable views removes one of the last remaining objections to using MySQL as a mainstream commercial database. For instance, prior to the introduction of stored procedures, MySQL could not claim Java J2EE certification, because the certification tests include stored procedure routines. While the “commercial” databases still include many features not found in MySQL, these features are often superfluous to the needs of mainstream database applications.

We believe that MySQL will continue to grow in significance as the premier open source RDBMS and that stored programs—procedures, functions, and triggers—will play a major part in the ongoing MySQL success story.

Tip

First, a note about this book’s title and terminology.

The IT industry, the media, and MySQL AB itself generally use the term stored procedures to refer to both stored procedures and stored functions. While this is technically inaccurate (a function is not a procedure), we felt that the title MySQL Stored Procedure Programming would most accurately and succinctly describe the purpose and content of this book. We also felt that the title MySQL Stored Procedure, Function, and Trigger Programming would just be too much of a mouthful!

To avoid any confusion, we use the general term stored program within this book to refer to the set of database routines that includes procedures, functions, and triggers, and to specific types of programs (e.g., stored procedures) when appropriate.

---

^[*] The Innovator’s Dilemma, Clayton Christensen (New York, 2000), HarperBusiness Essentials.

^[*] MySQL has a dual licensing model that allows for free use in many circumstances but does require a commercial license in some circumstances.