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 */;

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.