How to make Emacs sql-mode recognize MySQL #-style comments?

Question

I'm reading a bunch of MySQL files that use # (to end-of-line) comments, but my sql-mode doesn't support them. I found the syntax-table part of sql.el that defines /**/ and -- comments, but according to this, Emacs syntax tables support only 2 comment styles.

Is there a way to add support for # comments in sql.el easily?

Answer 1

You can define ?# to start comment-style b, which means there are two ways of starting the alternative comment style (either -- or #):

(setq sql-mode-syntax-table
  (let ((table (make-syntax-table)))
    ;; C-style comments /**/ (see elisp manual "Syntax Flags"))
    (modify-syntax-entry ?/ ". 14" table)
    (modify-syntax-entry ?* ". 23" table)
    ;; double-dash starts comments
    (modify-syntax-entry ?- ". 12b" table)
    (modify-syntax-entry ?# " b" table)
    (modify-syntax-entry ?\f "> b" table)
    ;; single quotes (') delimit strings
    (modify-syntax-entry ?' "\"" table)
    ;; double quotes (") don't delimit strings
    (modify-syntax-entry ?\" "." table)
    ;; backslash is no escape character
    (modify-syntax-entry ?\\ "." table)
    table))

(This was copied from sql.el and modified, which means that this is GPL)

Answer 2

The answer by Rolf did not seem to work for me. AFAIK, the character class for starting comments, of the alternative comment style should be "< b", not " b". This what I use:

    (add-hook 'sql-mode-hook 'my-sql-mode-hook) 
    (defun my-sql-mode-hook () 
      (define-key sql-mode-map (kbd "RET") 'newline-and-indent)

      ;; Make # start a new line comment in SQL. This is MySQL-specific
      ;; syntax.

      (modify-syntax-entry ?# "< b" sql-mode-syntax-table)
      (set-syntax-table sql-mode-syntax-table))