Ask the Expert

Using MySQL 5's new features

I read about the new features in MySQL 5. Can you describe some situations in which those new bells and whistles (stored procedures, cursors, views, etc.) might be valuable?

    Requires Free Membership to View

Sure, let's look at some of the flagship features:

Stored Procedures are procedural code that is stored within the MySQL server and which can be executed entirely on the server-side. MySQL uses standard SQL:2003 stored procedure language, also known as PL/PSM. Here is an example of a stored procedure:

CREATE PROCEDURE procedure1                /* name */
(IN parameter1 INTEGER)                    /* parameters */

The preceding procedure takes a single integer parameter and inserts either "birds" or "beasts" into a table depending on whether or not the parameter equals 17.

While stored procedures are not for everyone, one compelling argument for using stored procedures is their portability: You can write business logic once in a stored procedure and then re-use it in a PHP front-end, a desktop application, and in a portable application by calling the stored procedure on the MySQL server. You can called the same stored procedure from C, PHP, Java, VB, or any language that can connect to MySQL. This can save a lot of effort because you do not have to re-implement the same logic over and over again.

You can learn more about stored procedures in an article here.

Triggers use the same procedural language as stored procedures, but rather than waiting for a user to call the code, it is attached to an event, such as an INSERT into a table. Events are typically INSERTs, UPDATEs and DELETEs. Here is an example of a trigger:

CREATE TRIGGER t25_bi
BEFORE INSERT ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

In this case the trigger checks the data being inserted into a table to see if the leftmost character being inserted into the s2 field is not 'A' and if so sets the value of the s1 column to 0. Triggers can be used for logging activity (say you want an audit table that shows each action taken on another table as a log), validating data (check whether inserted values meet a complicated criteria), and maintaining relationships in a table handler that does not enforce foreign keys.

You can learn more about triggers in an article here.

A view is "a named, derived table whose definition is a persistent part of the database." Think of a view as a stored query that can be used as a table. Here are a couple of examples of views:

CREATE VIEW v AS SELECT column1 FROM t;

CREATE VIEW v AS
SELECT * FROM t
WHERE col1 IN (SELECT MAX(col1) FROM t);

Using views simplifies use of MySQL. If a complicated query is used for business reporting, it is often easier to turn it into a view, then end users can use the view to create reports. Views can also help with security; if a user should only have access to a subset of the rows in a table, create a view and grant access to it instead of the entire table (say for example you wanted to provide access to only a single region's sales data in a multi-region table).

You can learn more about views in an article here.

How you use the new features of MySQL will depend on your application, and your level of expertise. Most users can benefit greatly from views without a lot of experience in procedural programming and would do well to give them a try.

This was first published in May 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: