11 Mar

SQL syntax with /*! c-style comments in MySQLdump

In mysql we have — , /* and /*! comments.  This post is mainly about very basic c-style comments.

/*! : C-Style comments in MySQL

We normally see comments in MySQLdump as follows:

/*!40000 ALTER TABLE `a` DISABLE KEYS */;
Or
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */

These are actually C-Style comments which has embeded sql and treated specially by MySQL server, but ignored by other database engines.
This helps in writing portable comments, it’s treated as a part of surrounding SQL by MySQL while ignored by other databases.

But still you may wonder what are these numbers after /*! – /*!40000 or /*!50013 !!

There are two ways you can write the C-Style comments.
1. /*! SQL */

When SQL is embeded with comments [/*! and */] it’s executed by MySQL server along with surrounding SQL.

2. /*!version-number SQL */

When SQL is embeded with comment that begins with /*! follwed by a version number, the SQL becomes version specific.
MySQL executes SQL body only if it server’s version is atleast as recent as version-number.

So if your MySQL version is 5.x, following syntax will give error.

SHOW /*!60000 status */;

but following will work fine!

SHOW /*!50000 status */;

[ad#ad-2-300×250]

Similarly see below example:
mysql> show /*!50077 full*/ tables;
+—————-+————+
| Tables_in_test | Table_type |
+—————-+————+
| comments       | BASE TABLE |
+—————-+————+
1 row in set (0.00 sec)

mysql> show /*!50078 full*/ tables;
+—————-+
| Tables_in_test |
+—————-+
| comments       |
+—————-+
1 row in set (0.00 sec)

Above code works at first attempt as MySQL Version was 5.0.77  but for version specific comment /*!50078 it don’t.


Other comments in MySQL:

  • – : double slash and a space or # : Hash

This is spanned till end of the line.

  • /* :

This is a normal multi-line comment.

One thought on “SQL syntax with /*! c-style comments in MySQLdump

Leave a Reply

-- Kedar Vaijanapurkar --