First I will say what the comment syntax is for various vendors' dialects. Then I will get specific about some matters that specifically affect MySQL or MariaDB.

Syntax Table

The first column is for the type of DBMS. "Standard" is the ISO/IEC SQL standard document. For the others, just click on the DBMS name to see the relevant documentation. The standard, incidentally, clarifies that strings of comments are to be treated as a newline, so if you hear somebody say "comments are ignored", that's slightly wrong.

The first column is for comments that begin with "--" (two hyphen-minus signs), what the standard document calls "simple comments", the ones that look like this:

SELECT * FROM t; -- SIMPLE COMMENT

Everyone supports simple comments, the only problem with MySQL/MariaDB is their insistence that the -- must be followed by a space. I've had it explained to me that otherwise the parser had problems.

The second column is for comments enclosed by /* and */, what the standard document calls "bracketed comments", the ones that look like this:

SELECT * FROM t; /* BRACKETED COMMENT */

According to the standard document, bracketed comments are not mandatory, they are optional feature T351. However, it would be surprising to find a modern SQL implementation that doesn't support them.

The third column is for comments that begin with "#" (what Unicode calls Number Sign but an American dictionary allows for the word Octothorpe ), the ones that look like this:

SELECT * FROM t; # OCTOTHORPE COMMENT

Notice how, in every row but the MySQL/MariaDB row, the key word is NO? In fact I've only encountered one other SQL DBMS that is octothorpophiliac: mSQL. Old-timers may recall that mSQL from Hughes Technologies was, for a while, an inspiration for one of MySQL's founders. Anyway, it's unnecessary because simple comments do the job just as well.

The fourth column is for nesting, that is, putting bracketed comments within bracketed comments, that look like this:

SELECT * FROM t; /* OUTER /* INNER */ COMMENT */

I've often been irritated that I can't nest in C, so I approve of the DBMSs that support this standard requirement. But I never saw it as important in my MySQL-architect days. There were a few what I guess could be categorized
as "feature requests" (here and here and here) and I approved of my colleagues' clear responses, it's low priority.

The final column is for hints. A hint is a bit of syntax that the server might ignore, signalled by an extra character or two in a bracketed comment, like this:

SELECT /*+ HINT COMMENT */ * FROM t;

Typically a hint is a suggestion for an optimizer, like "use index X instead of the default". It's found in Oracle; it's not found in PostgreSQL and some PostgreSQL folks don't like it; but it's found in EnterpriseDB's "advanced PostgreSQL"; and of course it's found in MySQL and MariaDB. A newish point is that MariaDB has an extra signal "/*M!###### MariaDB-specific code */" that MySQL won't recognize, which is a good thing since the optimizers have diverged somewhat.

Passing comments to the server

In the MySQL 5.7 manual we see the client has an interesting option:

--comments, -c
Whether to preserve comments in statements sent to the server. The default is --skip-comments (discard comments), enable with --comments (preserve comments).

and a good question is: huh? Surely we should preserve comments, especially in stored procedures, no? Well, the obvious answer is that the parser has to spend time skipping over them, but I doubt that the effect is significant nowadays. The better answer is merely that behaviour changes are serious so let's leave this up to the users. Our GUI client supports --comments too, which is no surprise since we support all mysql-client options that make sense in a GUI.

But what happens if it's hard to tell where comments belong? Buried in the source download is a file named mysql-test/t/mysql_comments.sql which is checking these questions:
* Ignore comments outside statements, on separate lines?
* Ignore comments at the end of statements, on the same line but after the semicolon?
* Ignore comments inside CREATE PROCEDURE/FUNCTION/TRIGGER/EVENT, but not in the body?
The test should be updated now that compound statements in MariaDB don't have to be inside CREATE PROCEDURE/FUNCTION/TRIGGER/EVENT.

Therefore

Steve McConnell's "Code Complete" book advises: "A common guideline for Java and C++ that arises from a similar motivation is to use // synax for single-line comments and /* ... */ syntax for larger comments."

I guess that the equivalent for SQL purposes would be to say: use -- for single-line comments and /* ... */ for longer ones. But don't use #, and be wary with standalone or endline comments, and turn --comments on.

Update

In an earlier blog post I predicted that ocelotgui, our GUI client for MySQL and MariaDB, would be beta in February. Now it's February 29, so I have to modify that to: "any day now (watch this blog for updates or click Watch on the github project page)". The latest feature additions are in the downloadable source code, by the way, but not in the binary release.