Preface - MySQL Stored Procedure Programming (2009)

MySQL Stored Procedure Programming (2009)


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.


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 Programmingwould 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.

Objectives of This Book

The new capabilities provided by stored procedures, functions, and triggers (we call these, in general, stored programs) require new disciplines for MySQL developers, only some of whom will have prior experience in stored program development using other relational databases. Wise use of stored programs will lead to MySQL applications that are more robust, reliable, and efficient. However, inappropriate use of stored programs, or poorly constructed stored programs, can lead to applications that perform poorly, are hard to maintain, or are unreliable.

Thus, we see the need for a book that will help MySQL practitioners realize the full potential of MySQL stored programs. We hope this book will help you to use stored programs appropriately, and to write stored procedures, functions, and triggers that are reliable, correct, efficient, and easy to maintain.

Best practice stored program development relies on four fundamentals:

Appropriate use

Used appropriately, stored programs can improve the performance, reliability, and maintainability of your MySQL-based application. However, stored programs are not a universal panacea, and they should be used only where appropriate. In this book, we describe where stored programs can be used to good effect, and we outline some significant patterns (and anti-patterns) involving stored programs.


As with any programming language, the MySQL stored program language allows you to write code that will behave predictably and correctly in all possible circumstances, but the language also allows you to write code subject to catastrophic failure or unpredictable behavior when unanticipated scenarios arise. We outline how to write stored programs that can deal appropriately with error conditions, that fail gracefully and predictably, and that are—to the greatest extent possible—bug free.


We have all had that sinking feeling of having to amend some piece of code—whether written by a colleague or by ourselves—and finding that the intention, logic, and mechanisms of the code are almost impossible to understand. So-called "spaghetti" code can be written in any language, and MySQL stored programs are no exception. We explain how to construct code that is easily maintained through best practice naming conventions, program structure, commenting, and other mechanisms.


Any nontrivial application has to perform to either implicitly or explicitly stated performance requirements. The performance of the database access code—SQL and stored program code—is often the most significant factor in overall application performance. Furthermore, poorly constructed database code often fails to scale predictably or at all when data or transaction volumes increase. In this book, we show you when to use stored programs to improve application performance and how to write stored program code that delivers the highest possible performance. The SQL within a stored program is often the most performance-critical part of the stored program, so we explain in depth how to write high-performance SQL as well.

[*] 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.

Structure of This Book

MySQL Stored Procedure Programming is divided into four major sections:

Part I, Stored Programming Fundamentals

This first part of the book introduces the MySQL stored program language and provides a detailed description of the language structure and usage.

§ Chapter 1, Introduction to MySQL Stored Programs, asks the fundamental questions: Where did the language come from? What is it good for? What are the main features of the language?

§ Chapter 2, MySQL Stored Programming Tutorial, is a tutorial that is designed to get you started with the language as quickly as possible; it shows you how to create basic stored programs of each type and provides interactive examples of major language functions.

§ Chapter 3, Language Fundamentals, describes how to work with variables, literals, operators, and expressions.

§ Chapter 4, Blocks, Conditional Statements, and Iterative Programming, explains how to implement conditional commands (IF and CASE) and looping structures.

§ Chapter 5, Using SQL in Stored Programming, discusses how SQL can be used within the language.

§ Chapter 6, Error Handling, provides the details of how errors can be handled.

Part II, Stored Program Construction

This part of the book describes how you can use the elements described in Part I to build functional and useful stored programs.

§ Chapter 7, Creating and Maintaining Stored Programs, outlines the statements available for creating and modifying stored programs and provides some advice on how to manage your stored program source code.

§ Chapter 8, Transaction Management, explains the fundamentals of transaction handling in stored programs.

§ Chapter 9, MySQL Built-in Functions, details the built-in functions that can be used in stored programs.

§ Chapter 10, Stored Functions, describes how you can create and use one particular type of stored program: the stored function.

§ Chapter 11, Triggers, describes another special type of stored program—the database trigger—which is activated in response to DML (Data Manipulation Language) executed on a database table.

Part III, Using MySQL Stored Programs in Applications

Stored programs can be used for a variety of purposes, including the implementation of utility routines for use by MySQL DBAs and developers. However, the most important use of stored programs is within applications, as we describe in this part of the book. Stored programs allow us to move some of our application code into the database server itself; if we do this wisely, we may benefit from an application that will then be more secure, efficient, and maintainable.

§ Chapter 12, Using MySQL Stored Programs in Applications, considers the merits of and best practices for using stored programs inside modern—typically, web-based—applications. The other chapters in this part of the book show you how to use stored procedures and functions from within the development languages most commonly used in conjunction with MySQL.

§ Chapter 13, Using MySQL Stored Programs with PHP, describes the use of stored programs from PHP. We primarily discuss the mysqli and PDO interfaces—recently bundled by MySQL asConnector/PHP—and their stored program support.

§ Chapter 14, Using MySQL Stored Programs with Java, describes the use of stored programs from Java and includes the use of stored programs using JDBC, Servlets, Enterprise JavaBeans?, Hibernate, and Spring.

§ Chapter 15, Using MySQL Stored Programs with Perl, describes the use of stored programs from Perl.

§ Chapter 16, Using MySQL Stored Programs with Python, describes the use of stored programs from Python.

§ Chapter 17, Using MySQL Stored Programs with .NET, describes the use of stored programs from .NET languages such as C# and VB.NET.

Part IV, Optimizing Stored Programs

This final part of the book hopes to take you from "good" to "great." Getting programs to work correctly is hard enough: any program that works is probably a good program. A great program is one that performs efficiently, is robust and secure, and is easily maintained.

§ Chapter 18, Stored Program Security, discusses the unique security concerns and opportunities raised by stored procedures and functions.

§ Chapter 19, Tuning Stored Programs and Their SQL. This chapter, along with Chapters 20 through 22, covers the performance optimization of stored programs. This chapter kicks off with a general discussion of performance tuning tools and techniques.

§ Chapter 20, Basic SQL Tuning. The performance of your stored programs will be largely dependent on the performance of the SQL inside them, so this chapter provides guidelines for tuning basic SQL.

§ Chapter 21, Advanced SQL Tuning. This chapter builds on Chapter 20, describing more advanced tuning approaches.

§ Chapter 22, Optimizing Stored Program Code, covers the performance tuning of the stored program code itself.

§ Chapter 23, Best Practices in MySQL Stored Program Development, wraps up the book with a look at best practices in stored program development. These guidelines should help you write stored programs that are fast, secure, maintainable, and bug free.

You'll find that a significant proportion of the book includes material that pertains not only to stored program development, but also to development in other languages such as PHP or Java. For instance, we believe that you cannot write a high-performance stored program without tuning the SQL that the program contains; therefore, we have devoted significant coverage to SQL tuning—material that would also be of benefit regardless of the language in which the SQL is embedded. Likewise, some of the discussions around transaction design and security could be applicable in other languages.

What This Book Does Not Cover

This book is not intended to be a complete reference to MySQL. It focuses on the stored program language. The following topics are therefore outside the scope of this book and are not covered, except in an occasional and peripheral fashion:

The SQL language

We assume that you already have a working knowledge of the SQL language, and that you know how to write SELECT, UPDATE, INSERT, and DELETE statements.

Administration of MySQL databases

While DBAs can use this book to learn how to write the code needed to build and maintain databases, this book does not explore all the nuances of the DDL (Data Definition Language) of MySQL's SQL.

Conventions Used in This Book

The following conventions are used in this book:


Used for URLs and for emphasis when introducing a new term.

Constant width

Used for MySQL and SQL keywords and for code examples.

Constant width bold

In some code examples, highlights the statements being discussed.

Constant width italic

In some code examples, indicates an element (e.g., a filename) that you supply.


In code examples, generally indicates MySQL keywords.


In code examples, generally indicates user-defined items such as variables, parameters, etc.


In code examples, enter exactly as shown.


In code examples, helps to show structure but is not required.

In code examples, begins a single-line comment that extends to the end of a line.

/* and */

In code examples, delimit a multiline comment that can extend from one line to another.


In code examples and related discussions, qualifies a reference by separating an object name from a component name.

[ ]

In syntax descriptions, enclose optional items.

{ }

In syntax descriptions, enclose a set of items from which you must choose only one.


In syntax descriptions, separates the items enclosed in curly brackets, as in {TRUE | FALSE}.


In syntax descriptions, indicates repeating elements. An ellipsis also shows that statements or clauses irrelevant to the discussion were left out.


Indicates a tip, suggestion, or general note. For example, we'll tell you if a certain setting is version-specific.


Indicates a warning or caution. For example, we'll tell you if a certain setting has some kind of negative impact on the system.

Which Version?

This book describes the stored program language introduced in MySQL 5.0. At the time the book went to press, MySQL 5.0.18 was the most recently available binary Community edition, although we were working with versions up to 5.1.7 built directly from source code.