Summary - MySQL

We’ve looked in this chapter at several key new features that are becoming available or are very likely to be over the next few versions of MySQL. All of these features will add significant functionality to MySQL, increasing its power and flexibility in different ways. It seems logical to approach future attractions in a version-by-version fashion, so we started with subqueries and other features implemented in MySQL 4.1, which is almost certain to be available in a production release before the end of 2004. Subqueries, or queries within queries, can often be used to make queries shorter, simpler, and easier to read. In some cases they can even allow us to do in a single SELECT what would otherwise require multiple queries to accomplish without them.

We’d like to remind you at this point that we covered the other major new advance in MySQL 4.1—a new client API—in Chapter 7. These new programming APIs include prepared statements and multiple statements and will make MySQL applications programming much more flexible and efficient.

We also looked at the ability to assign table indexes to separate caches for purposes of fine-tuning key caching, which (beginning in MySQL 4.1.1) can be used to make indexes more efficient.

MySQL 5.0 will provide an implementation of another key SQL concept: that of what are sometimes known as SQL control statements. This is another way of expressing the idea of packaging collections of SQL statements so that they can be reused. Databases that do this implement what are known as stored procedures and stored functions, and both of these were already present in the first pre-release version of MySQL 5 (version 5.0.0-alpha) that became available at the end of 2003. While it will likely be sometime in 2005 or 2006 before these are included in a production release, you can download the pre-release from http://dev.mysql.com now, test out the examples we included, and try your hand at writing your own. We also looked at cursors, which provide a way to point at a particular row within a result set and work with its contents.

Beginning with version 5.0.1, MySQL also implements views, which provide a means of defining what are sometimes known as virtual tables, based on queries of existing tables (and sometimes even of other views). Views are extremely powerful, not only because they can provide simplified real-time access to normalized data that would otherwise require complicated joins, but also due to the fact that in many cases they can also be used to update the tables on which they’re based. In addition, views can help us ensure that read and update access to table data is properly secured.

Looking further ahead, it seems likely that MySQL 5.1 will implement another key SQL feature—triggers. These provide a way to call stored procedures automatically in response to changes made in tables or views. For example, if a query will delete a record from a table, you can (or will be able to) use a trigger to copy data from that record to another table before it’s actually deleted.

We saw how stored procedures and views (along with triggers when they’re implemented) can make life easier for database designers, developers, and users and can help enhance data security as well. So far as MySQL 5.0 and 5.1 are concerned, we’ve only scratched the surface here. Complete coverage of stored procedures and views each could easily take up entire chapters—and in future books about MySQL, they very likely will. And while we discussed some other expected future improvements, there will doubtless be new additions that we’ve either not had space to cover here or haven’t been able to foresee. So, as you use MySQL and upgrade to new versions, watch the MySQL web site, and check the documentation for news about new features and changes in existing ones.