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!)