In this article, Vikram gives us a sneak-peek under the hood of MySQL to see what makes it tick, all the while explaining the various MySQL subsystems and how they interact with each other. This excerpt comes from Chapter two of MySQL: The Complete Reference (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).
In most RDBMS products, you can extend the capabilities of the database by using stored procedures. The programmability is usually further extended by enhancements to SQL that contains control-of-flow statements and conditional logic, as SQL Server does with T-SQL and Oracle with PL/SQL.
As of yet, MySQL includes no support for stored procedures, but one of the great benefits of this RDBMS is its extensibility. In keeping with its open-source roots, MySQL makes the original source code available as part of the distribution, which permits developers to add new functions and features that are compiled into the engine as part of the core product. MySQL also allows separate C and C++ libraries to be loaded in the same memory space as the engine when MySQL starts up.
Either of these methods will allow users to interact with your functions in the same way as they would with any of the already built-in functions available in MySQL, such as SUM() or AVG(). Because these functions run in the same memory space as MySQL, and because they execute on the server where MySQL is located, using them minimizes network traffic between the calling program and the server, thereby vastly increasing performance.
You can add functions to MySQL through a special user-defined function interface. User-defined functions are created initially as special C/C++ libraries and are then added and removed dynamically by means of the CREATE FUNCTION and DROP FUNCTION statements. User-defined functions come with the added burden of having to install all your libraries with every installation of MySQL; however, this does make deployment faster, since you can load these functions into the binary distribution rather than having to go through the trouble of compiling MySQL all over again to incorporate the new functions. In addition, there’s generally a good chance that your libraries will continue to work unchanged as new versions of MySQL are released.
You can also add functions as native (built-in) MySQL functions. Native functions are compiled into the MySQL server engine and become a permanent part of the MySQL installation itself. This makes new installations much easier than using libraries, because once you have defined your distribution, you can be sure that all of your subsequent installations will contain the functions that you have added. On the other hand, you must recompile and re-create your distribution for every new release of MySQL.
Getting Creative
You can simulate the encapsulation and centralization of a stored procedure in MySQL by storing code in a table and then retrieving the text of that code to be executed by the calling program. For example, a Perl program could have a table that includes rows containing nothing but Perl code. The client program would retrieve the needed code block and evaluate the code at run time.
This technique lets you place logic at the server without requiring that client programs be aware of any code changes. Because of the multiple round trips to and from the server, and because this approach works only with interpreted languages, it is not a true replacement for all the capabilities that one gets from stored procedures in other RDBMS implementations...but it’s close.
Remember: this is chapter two of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed. Buy this book now.
/ 30 
