SQL Mode ANSI_QUOTES

Jun 23, 2008 / By Sheeri Cabral

Tags:

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 changed
mysql> select @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> create table `table with space` (num int);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from `table with space`;
Empty set (0.00 sec)

mysql> select * from "table with space";
ERROR 1064: 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 with space"' at line 1
mysql> select * from 'table with space';
ERROR 1064: 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 with space'' at line 1

And here we chnage the SQL mode, and show that ” acts like ` in SQL_MODE=ANSI_QUOTES:

mysql> SET @@session.sql_mode=ANSI_QUOTES;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_MODE;
+-------------+
| @@SQL_MODE  |
+-------------+
| ANSI_QUOTES |
+-------------+
1 row in set (0.00 sec)

mysql> select * from `table with space`;
Empty set (0.00 sec)

mysql> select * from "table with space";
Empty set (0.00 sec)

mysql> select * from 'table with space';
ERROR 1064: 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 with space'' at line 1

mysql>

If you’re wondering why all your queries are giving strange results such as what we saw above, or even ERROR 1054: Unknown column 'col_name' in 'field list' if what you are quoting is a string that MySQL is interpreting as a column name, check your SQL mode.

One Response to “SQL Mode ANSI_QUOTES”

  • “Normally the functionality of double quotes is more like that of single quotes (’).”

    Normal for my MySQL yes, not the rest of the DB world, or perhaps maybe MS Access.

    “one of the more annoying things about Oracle is that I keep forgetting I can’t use “, only ‘”

    Heh, for me it is the other way around ;)

    Anyway, I used to have it in my default SQL mode, and it is incredible how many applications break. App developers, pls always use single quotes for your strings, and backticks for your MySQL quoted identifiers.

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>