ABIS Infor - 2011-08

MySQL: new features since version 5.0

Peter Vanroose (ABIS) - 4 July 2011

Abstract

MySQL went through several important evolutions since its birth in 1995. It was mainly the switch from version 4.1 to 5.0, introducing constructs like views, triggers, and stored procedures, which marked the transition of an underdog RDBMS to a full-grown database server able to compete with its "big brothers". A brief historic overview of MySQL and a summary of what was added in MySQL 5.0 will help understanding the big potential of this great RDBMS!

MySQL: a relational database

MySQL is an RDBMS which distinguishes itself from others like DB2, Oracle, or SQL Server, mainly by the fact that it is an open source product: its implementation (written in C and C++) is available for anyone, and can hence be modified and extended by any interested programmer. Lots of individuals have contributed to the implementation of MySQL since its "birth" in May 1995. This is at the same time a strength and a weakness: the MySQL database server supports a rich set of syntactic (SQL) features, several of these being nonstandard extensions which only MySQL provides. Luckily there are enough "unifying" forces to avoid MySQL becoming a chaotic amalgam. As such, MySQL is often cited as one of the most successful open source software products, next to Linux, Open Office, Java, Perl, Firefox, and Apache, to name just a few.

MySQL gradually became the de facto standard RDBMS component for web-based content management: sites like Google, Wikipedia, Facebook, YouTube, CMS systems like Drupal, and lots of companies and government instances worldwide, are using MySQL as their website data engine. MySQL's popularity started about 8 years ago, when version 4.0 became generally available. Since then, it went through several important evolutions, both in terms of its technical possibilities and because of its alliances, first with Innobase in 2000, then its acquisition by Sun Microsystems in February 2008, and last but not least the acquisition of Sun by Oracle in January 2010.

Technically, the three most important landmarks were the adoption of InnoDB in 2001 (allowing for referential integrity and transational data consistency, viz. locking, commit, and rollback), and the releases of version 4.0 in the fall of 2002 and of version 5.0 in the fall of 2004. More recent important landmarks are more techical in nature, and made the platform more stable and more scalable.

New developments since version 5.0

What has changed in MySQL since 2004? A lot! One can say that with version 5.0 MySQL left its adolescence phase and gradually (with versions 5.1 and 5.5) became a mature RDBMS. Version 5.0 introduced several new SQL syntactic possibilities, both DDL and DML. The most important new SQL-syntactic features of 5.0 were: nested queries, (updatable) views, triggers, stored procedures, server-side cursors, and table partitioning.

MySQL 5.0 is no longer actively supported, as of this year. Currently, most sites use version 5.1 which saw the light in 2008. In December 2010, version 5.5 was released. (Versions 5.2, 5.3 and 5.4 do not exist.; 5.4 was announced in 2009 but never released.) The MySQL development team is currently working at versions 5.6 (already available as a development release) and 6.0 (alpha release). Not so much new features, but rather robustness and scalability are being addressed in these new releases.

Clearly, MySQL keeps evolving, slowly but steadily, towards a fully standard RDBMS server with a versatile usability.

DDL novelties

Although most sites are currently running on MySQL 5.1, many of them have not yet started using the "new" features of 5.0! More specifically, the following kinds of objects can now be used (but are often neglected, maybe just because of a lack of knowledge by the database maintainers?)

Views: CREATE VIEW name AS SELECT ....

The view "name" can now be used with DML (select, insert, update, delete) as if it were a table. Even a view which is defined as a join or which has expressions as column definition will be insertable (with some limitations) and updatable!

Triggers: CREATE TRIGGER name AFTER INSERT ON tbname FOR EACH ROW SET @var = NEW.colname

A trigger is automatically "triggered" as a consequence of some particular DML action. Both "before" and "after" triggers are supported, for insert, update, or delete; BEGIN...END blocks can be used when multiple statements are to be put in the trigger body. Example:

	CREATE TRIGGER x BEFORE UPDATE ON tbl FOR EACH ROW
	BEGIN
		IF NEW.col<0 THEN SET NEW.col=0;
		ELSEIF NEW.col>100 THEN SET NEW.col=100;
		END IF;
	END

Stored procedures: functional objects, to be executed when explicitly called with the CALL sql statement.
User-defined functions: functional objects, created through CREATE FUNCTION, and to be called in a similar way as the built-in functions, that is, anywhere in DML where an expression can be used.
New storage engines: as of MySQL 5.0, the "federated" engine was added, which allows access to table data which physically resides on a different (remote) server. Also the "archive" storage engine was added, allowing to store not-so-often used voluminous data in an storage-efficient way. This engine does not allow UPDATE or DELETE, and doesn't support indexes. MySQL 5.5 changed the default storage engine from MyISAM to InnoDB.
A new datatype: the "BIT(n)" datatype was added in MySQL 5.0. It allows to store n bits of data (0 or 1), with n ranging from 1 to 64, in the most compact way, that is, using just one out of the 8 bits in a byte per entry. Note that BIT(n) is a non-standard SQL datatype.
The INFORMATION_SCHEMA database: MySQL is one of the first RDBMS (besides PostgreSQL) to implement the standard metadata database (also called catalog) which documents the objects present on the server. This database has tables called TABLES, COLUMNS, SCHEMATA, VIEWS, TRIGGERS, and a few more. For example, a row in the TABLES table contains all information that is pertinent to one particular table on the server. Suppose a table named tbaccad.tutpersons exists; then the meta-table TABLES will have a row with TABLE_SCHEMA='tbaccad', TABLE_NAME='tutpersons', TABLE_TYPE='BASE TABLE' (a "real" table, not a view), ENGINE='InnoDB', TABLE_ROWS=45, ... The column definitions (names and data types) go in INFORMATION_SCHEMA.COLUMNS, of course. And the constraints (unique indexes, foreign keys, check constraints) are documented in the table INFORMATION_SCHEMA.TABLE_CONSTRAINTS.
Table partitioning was added in MySQL 5.1. It allows to store subsets of rows of one table across your file system, e.g. on different disks. Otherwise said: the partitions are physically manipulated as if they were different tables, while logically they are presented to DML statements (select, insert, update, delete) as belonging to a single table. As if you would define a view with a UNION, but such a view would not allow INSERTs. Partitioned tables have in their definition a range specification which unambigously associates a certain table row (based on its content, often the value of just one of its columns) with exactly one of the available partitions.

And finally ...

Want to learn more about MySQL? Check out our 3-day "MySQL basics" course (see http://www.abis.be/html/en1523.html).