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

Kedar

15 years ago

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

/*!50013 DEFINER=`nitty_witty_com`@`%` 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.

Further this can be used for adjusting the syntax or keywords change in latest releases. For eg MySQL 8.0.26 has replaced the word “slave” with “replica” in variables and you can use your query to hint the execution as:

SELECT @@global.version as version, @@global.hostname as service \
/*!80026 , @@global.replica_exec_mode as replica_exec_mode */ \
/*!80026 , @@global.replica_skip_errors as replica_skip_errors */\G

/*! : C-Style comments in MySQL

Other comments in MySQL: