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> SELECT `alive`;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'
mysql> CREATE TABLE table (column date);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table (column date)' at line 1
mysql> CREATE TABLE `table` (column date);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column date)' at line 1
mysql> CREATE TABLE `table` (`column` date);
Query OK, 0 rows affected (0.11 sec)
mysql> DROP TABLE table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1
mysql> DROP TABLE `table`;
Query OK, 0 rows affected (0.00 sec)
The above illustrates MySQL’s default behavior, which in summary is:
- With no quotes or backquotes, MySQL treats the word ‘alive’ as a field name.
- With single or double quotes, MySQL treats the word ‘alive’ as a string.
Changing SQL Mode
However, this behavior can be changed by setting the sql_mode. There are over 30 different sql modes in MySQL 5.1 that change the behavior of MySQL (not all are related to quoting). The sql_mode value is a comma-separated list showing which sql modes are used.
The default sql_mode is blank, allowing default MySQL behavior.
If sql_mode is changed to ANSI_QUOTES, then ANSI quotes are used….
mysql> SET SESSION SQL_MODE='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| sql_mode | ANSI_QUOTES |
+---------------+-------------+
1 row in set (0.00 sec)
MySQL still treats barewords and backquotes as columns:
mysql> SELECT alive;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'
mysql> SELECT `alive`;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'
MySQL still treats single quotes as strings:
mysql> SELECT 'alive';
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)
However, now MySQL treats double quotes as escape characters, ie, treating double quotes like backquotes:
mysql> SELECT "alive";
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'
Putting quote marks in strings
Since I am discussing quoting, I will end with how to put quote marks into strings.
There are 3 ways to embed quotes in a string:
1) mix single and double quotes:
mysql> SELECT 'I say "OK".' , "I say 'OK'." , 'I say ''OK''.';
+-------------+-------------+-------------+
| I say "OK". | I say 'OK'. | I say 'OK'. |
+-------------+-------------+-------------+
| I say "OK". | I say 'OK'. | I say 'OK'. |
+-------------+-------------+-------------+
1 row in set (0.00 sec)
2) Use 2 quotes in a row to escape quotes:
mysql> SELECT 'I say ''OK''.' , "I say ""OK"".";
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
1 row in set (0.00 sec)
3) Use the backslash to escape quotes:
mysql> SELECT 'I say \'OK\'.' , "I say \"OK\".";
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
1 row in set (0.00 sec)
(Interestingly enough, I wrote a blog post on this topic almost exactly 2 years ago, too!)
PlanetMySQL Voting:
Vote UP /
Vote DOWN