MySQL Trick with Comments

March 21, 2009


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`@``*/ PROCEDURE `make_tag_cloud`()

The “MySQL manual”: 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.

5 Responses to “MySQL Trick with Comments”

  1. Registryrid Says:

    also u can login and use database name and source < xxx.sql to import mysql.

  2. bhawin13 Says:

    I am using mysqldump on daily basis, and I was removing this comment by considering it as comment.

    But now I will not do that. I will keep this in sql files.

    Thanks for the good tips.

    I hope we can see really helpful tip in future also.

  3. rodlex377 Says:

    O boy..I still needs more learning experience on PHP before I can really get my dirty hands gets the craft. But I like this one. This page is now bookmarked. thanks a lot. Nice post.

  4. asdasd876 Says:

    mysqldump db -u USER -p > output_file.sql
    mysql -u USER -p DB < output_file.sql

    note: using -p prevents having the password viewable in the process list

  5. antrecu Says:

    just in case when I dump a mysql db I use this command: mysqldump db_name –user=db_username –password=db_usernamepassword < /path_to_file.sql and to restore the dump use mysql db_name –user=db_username –password=db_usernamepassword > /path_to_file.sql these commands are pretty basic but very very helpful :)