The world's most popular open source database
Peter Gulutzan is a Software Architect at MySQL AB, and author of SQL Performance Tuning. He's also another Canadian member of the MySQL Documentation Team.
By Peter Gulutzan
This book is for long-time MySQL users who want to know "what's new" in version 5. The short answer is "stored procedures, triggers, views, information_schema". The long answer is the "MySQL 5.0 New Features" series, and this book is the second in the series.
What I'm hoping to do is make this look like a hands-on session where you, as if you're working it out yourself on your keyboard, can walk through sample problems.
To do this, I'll go through each little item, building up slowly. By the end, I'll be showing larger routines that do something useful, something that you might have thought was tough.
Whenever I want to show actual code, such as something that comes directly from the screen of my mysql client program, I switch to a Courier font, which looks different from the regular text font. For example:
mysql> DROP FUNCTION f; Query OK, 0 rows affected (0.00 sec)
When the example is large and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page. For example:
mysql> CREATE PROCEDURE p () -> BEGIN -> /* This procedure does nothing */ -> END;// Query OK, 0 rows affected (0.00 sec)
Sometimes I will leave out the "mysql>" and "->" prompts so that you can cut the examples and paste them into your copy of the mysql client program. (If you aren't reading the text of this book in a machine-readable form, try looking for the script on the mysql.com web site.)
I tested all the examples with the publicly-available alpha version of MySQL 5.0.3 on Linux, SUSE 9.2. By the time you read this, the version number will be higher and the available operating systems will include Windows, Sparc, and HP-UX. So I'm confident that you'll be able to run every example on your computer. But if not, well, as an experienced MySQL user you know that help and support is always available.
We are including support for triggers in MySQL 5.0 for these reasons:
INSERT, UPDATE, and
I assume that you have read the first book in the "MySQL New Features" series already. In that book, "MySQL Stored Procedures", you (I hope) saw how MySQL supports stored procedures and functions. That's important knowledge, because you can use the same statements in triggers as you can use in functions. Specifically:
BEGIN / END) are legal.
IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are legal.
DECLARE) and assignment (
SET) are legal.
But remember that functions are subject to severe limitations: you cannot access tables from within a function. So these statements are illegal inside a function:
ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL LOCK OPTIMIZE REPAIR REPLACE REVOKE ROLLBACK SAVEPOINT 'SELECT FROM table' 'SET system variable' 'SET TRANSACTION' SHOW 'START TRANSACTION' TRUNCATE UPDATE
Precisely the same limitation applies for triggers.
The whole article is long, so we thought it better to make it a PDF. To download, click here (no registration required!) You could even print out the PDF so you can peruse at your leisure.