MySQL does not follow the ANSI SQL standard for quoting. MySQL’s default quoting behavior is that either single or double quotes can be used to quote a string (this gets me into trouble when I work with Oracle databases, as double quotes do not indicate a string!).

mysql> SELECT 'alive';
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

mysql> SELECT "alive";
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

Bare words are dealt with in context; in this case, a bare word would be parsed as a column name:

mysql> SELECT alive;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

Backquotes are the way MySQL escapes table names. So, if you want a reserved word, number or operator to be the name of an object (ie, a table named “1″ or a column named “date”) you need to use backquotes to avoid a syntax error….for example:

mysql> …

I was asked today about the ANSI_QUOTES SQL mode.

According to http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html, ANSI_QUOTES mode changes the functionality of double quotes (”) to be like the backtick (`). Normally the functionality of double quotes is more like that of single quotes (’).

You might use this when you have a table with spaces or other special characters you would like to escape, without having to use the backtick key. This is also ANSI standard SQL behavior (one of the more annoying things about Oracle is that I keep forgetting I can’t use “, only ‘).

Here is an example in the MySQL default mode — allowing ” to be more like ‘ :

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database …