home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
C
What is available?ABIS Infor » 2017-09 » 2017-01 » 2016-11 » 2016-06 » 2016-04 » 2016-02 » 2015-03 » 2014-03 » 2013-04 » 2013-01 » 2012-10 » 2012-06 » 2012-04 » 2012-01 » 2011-11 » 2011-10 » 2011-08 » Java Frameworks - Can you see the wood for the trees? » MySQL: new features since version 5.0 » The pillars of our services » SAS and the adaptation to the RDBMS world » TOGAF » 2011-06Exploring Db2Presentations and papersTest your knowledge 

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?)

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!

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

And finally ...

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