MySQL and Quoting

Jun 25, 2010 / By Sheeri Cabral

Tags: , ,

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

One Response to “MySQL and Quoting”

  • billy says:

    hi,

    mysql> select ‘\x2134′;
    +———+
    | x2134 |
    +———+
    | x2134 |
    +———+
    1 row in set (0.00 sec)

    mysql> select ‘x2134′;
    +——–+
    | x2134 |
    +——–+
    | x2134 |
    +——–+

    could I get the character what I input like the same output ?

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>