Stored procedures allow you to move business logic from end applications to the MySQL server, which can save effort when you have multiple end applications (instead of implementing a price calculation routine in PHP for the web and VC for the desktop, you can implement it in a stored procedure and call it from both PHP and VC).

Stored procedures can also enhance security by allowing you to prevent a user from accessing a table directly, but instead giving them permission to call a stored procedure that has access to the table.

Triggers are stored routines that are written with a syntax like that of stored procedures, but instead of being called by a user or application, triggers are activated by table events such as inserts, updates, and deletes. The triggers can modify or abort the triggering table actions, or take the information provided by the action and use it elsewhere.

Views are essentially named virtual tables defined by SQL queries. I can take a complex set of tables, create a SELECT query that might be more meaningful to end users, then turn it into a view. The end users can then perform SELECT queries against the view and, in most cases, also execute INSERT, UPDATE, and DELETE statements against the view, with the changes being reflected in the underlying tables. Views help to simplify complicated data and also enhance security. As with a stored procedure, you can create a view and give a user permission to SELECT from the view but not the underlying table.

Evaluating MySQL 5

In the end most users will eventually move to MySQL 5 as MySQL 4 and 4.1 age and become outdated. The timing will have to depend on whether you consider the features of MySQL 5 to be useful to your organization.

When evaluating the new features, I would recommend installing MySQL 5 on a separate machine and trying it with your existing applications, run as many tests as you can and see if everything runs smoothly. If you do not encounter any errors you can then bring MySQL 5 into your production systems (after a production release of MySQL 5).

There really should not be things to watch out for, in the sense of bugs or incompatibilities, but you can always watch the changelogs at the 5.0 news page on MySQL’s site and look for entries that are marked ‘Incompatible Change’.