More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Functionality Missing from MySQL - `--' as the Start of a Comment
Some other SQL databases use `--' to start comments. MySQL has `#' as the start comment character, even if the mysql
command-line tool removes all lines that start with `--'. You can also use the C comment style /* this is a comment */ with
MySQL.
MySQL Version 3.23.3 and above supports the `--' comment style only if the comment is followed by a space. This is because
this degenerate comment style has caused many problems with automatically generated SQL queries that have used something
like the following code, where we automatically insert the value of the payment for !payment!:
UPDATE tbl_name SET credit=credit-!payment!
What do you think will happen when the value of payment is negative?
Because 1--1 is legal in SQL, we think it is terrible that `--' means start comment.
In MySQL Version 3.23 you can, however, use: 1-- This is a comment
The following discussion only concerns you if you are running a MySQL version earlier than Version 3.23:
If you have a SQL program in a text file that contains `--' comments you should use:
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql database
instead of the usual:
shell> mysql database < text-file-with-funny-comments.sql
You can also edit the command file ``in place'' to change the `--' comments to `#' comments:
shell> replace " --" " #" -- text-file-with-funny-comments.sql
Change them back with this command:
shell> replace " #" " --" -- text-file-with-funny-comments.sql
(Continued on next question...)