O’Reilly news

MySQL Stored Procedure Programming: A Guide to Using Stored Procedures, Functions, and Triggers in MySQL

April 21, 2006

Sebastopol, CA--Five years ago, the capabilities of MySQL were so far behind the requirements of the majority of business users that its use in a business environment was almost unheard of. Said businesses opted instead for the commercial players in the field: Oracle, IBM, and Microsoft. But MySQL--being free or extremely low cost--had a definite appeal for users who couldn't afford a commercial relational database. As with most open source technologies, MySQL also experienced rapid technological development, adding transactions, subqueries, and other features typically associated with its commercial counterparts. By the release of version 4.0, MySQL was being used by an increasing number of high-profile companies, including Yahoo! and Google.

Now, with the 5.0 release, MySQL has crossed one of the last remaining thresholds for enterprise credibility, observes Guy Harrison, author of MySQL Stored Procedure Programming (O'Reilly, US $44.99) with Steven Feuerstein. "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," Harrison says. For instance, he notes that prior to the introduction of stored procedures, MySQL couldn't claim Java J2EE certification, because the certification tests included stored procedure routines. There are still many features in the commercial databases that aren't found in MySQL, Harrison agrees, but 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," write Harrison and Feuerstein in the preface to the book, "And that stored programs--procedures, functions, and triggers--will play a major part in the MySQL success story."

MySQL Stored Procedure Programming is rich in code examples and covers everything from language basics and application building to advanced tuning and best practices. A companion web site contains thousands of lines of code that readers can put to use immediately. The book consists of four main sections:

  • Stored programming fundamentals: tutorial, using SQL in stored programs, and error handling
  • Building MySQL stored programs: transactions, built-in functions, stored functions, and triggers
  • MySQL stored programs in applications: using stored programs with PHP, Perl, Python, Java, and .NET (C# and VB.NET)
  • Optimizing MySQL stored programs: security, basic and advanced SQL tuning, optimization of stored program code, and programming best practices.
  • According to Harrison, the book is written "primarily for MySQL developers who are currently developing applications involving MySQL in other environments, such as PHP, Python, Java, .NET, or Perl. Also MySQL administrators who might want to use stored programs to develop utility programs." He adds, "Stored procedures are new to MySQL and offer new patterns for application development. We hope to provide useful guidance so developers will use them appropriately and effectively."

    Although few in the MySQL world have substantial experience with stored procedures, Guy Harrison and Steven Feuerstein have decades of combined experience. Harrison is Chief Architect of Database Solutions at Quest Software. Feuerstein is the author of the classic reference for Oracle stored programming, Oracle PL/SQL Programming (O'Reilly). Between them, they have authored a dozen books.

    Advance Praise for MySQL Stored Procedure Programming:

    "I didn't honestly believe a book could be written on this topic that wouldn't be too dry. But Guy and Steven show the depth of the subject and make the material available to readers. It was a wonderful read."
    --Brian Aker, Director of Architecture, MySQL AB

  • Further praise for the book
  • Additional Resources: