First, I assume you understand the basic functionality of "code-containing comments."
/*!40101 some_sql_statement */
This means "only execute some_sql_statement
if you are a MySQL Server, version 4.01.01 or higher." So, there's no real need to wrap the statements in the comment syntax unless the dump files you are creating are going to handle backwards-compatibility with older versions of MySQL (or you want your backups to look exactly like mysqldump files, which isn't a bad idea). This is why the numbers vary -- the version indicates, for each statement, the version of MySQL server that would understand the statement. This is an attempt to make the files generated by mysqldump
to remain as backwards-compatible as possible.
But, onto the substance of your question.
Consider the statement you used as an example:
/*!40101 SET @saved_cs_client = @@character_set_client */;
This is a bad example, because it actually is static statement you can and should hard-code. From the source code to mysqldump, it's pretty low tech:
fprintf(sql_file,
"/*!50003 SET @saved_cs_client = @@character_set_client */ %s\n"
"/*!50003 SET @saved_cs_results = @@character_set_results */ %s\n"
"/*!50003 SET @saved_col_connection = @@collation_connection */ %s\n"
...
The %s
above is just the statement delimiter (";
").
This is all in switch_cs_variables()
inside client/mysqldump.c
in the source files.
The next three lines from the same code use information that mysqldump learned from queries it sent to the server:
"/*!50003 SET character_set_client = %s */ %s\n"
"/*!50003 SET character_set_results = %s */ %s\n"
"/*!50003 SET collation_connection = %s */ %s\n",
Looking at the source, the values used here are from character_set_client
, character_set_client
(again, it's used twice), and collation_connection
from the results of of SHOW CREATE [ trigger | function | procedure |event ]
.
Indeed, in thinking about this question, I remembered that I once wrote a similar script that generates a partial dump file, using perl. After collecting the data, I write them like this:
print "SET character_set_client = $def->{character_set_client};\n";
print "SET character_set_results = $def->{character_set_client};\n";
print "SET collation_connection = $def->{collation_connection};\n";
Note that I didn't bother with the version-specific comment wrapper since I don't have any need to replay these files on any system that is old enough not to recognize these statements.
Note, also, that the static statements -- that is, the SET @saved...
statements ... as well as (later in the dump, not shown above) SET @@... = @saved...
statements, are simply there to set the session back to its prior state after each object is created.
Don't forget that when you're selecting data from the server, MySQL has its own built-in QUOTE()
function which will do more correct quoting of string literals (the data you're selecting, for insert) than anything you roll yourself.