MySQL Trick with Comments

MySQL has a custom extension (if you want to call it that) to it’s commenting system, which actually allows you to make conditional commands. The mysqldump program that many people use to back up their databases uses this concept heavily, which is where I first ran into it.

In it’s simplest form, this allows you to place commands in a SQL file that only MySQL will run:

@/*! MySQL specific code */@

This allows you to make a single SQL file, that not only will execute correctly (and in detail) on MySQL, but it will also work on any ANSI compatible database, since you’ve hidden any of the non-ANSI parts in MySQL conditional comments. So for example, MySQL allows you to set a variable declaring that all names will be a certain character set, such as UTF-8, and therefore you can do:

@/*! SET NAMES utf8 */;@

This will cause the command to only be run on MySQL servers.

But wait, it turns out that this character set functionality wasn’t added until version 4.01.01 of MySQL, so you don’t want to run that on an older server. In those cases, you can add a version number of MySQL after the !

@/*!40101 SET NAMES utf8 */;@

Now that only gets run on MySQL 4.01.01 and later.

This can also be done in the middle of statements, to hide extra keywords, such as MySQL’s STRAIGHT_JOIN:

@SELECT /*! STRAIGHT_JOIN */ column FROM table,other WHERE …@

And again use this it inline with a specific version as needed, such as to send the ‘definer’ keyword when making a stored procedure if the version is 5.00.20 or later:


CREATE /*!50020 DEFINER=`root`@`127.0.0.1`*/ PROCEDURE `make_tag_cloud`()


The “MySQL manual”:http://dev.mysql.com/doc/refman/5.1/en/comments.html mentions all of this very briefly, and it took me a little while to find the information. Hopefully this little article at least will let you understand what these interesting looking comments actually do, if not give you interesting ideas of unique ways to use them.