MySQL: Quotation Marks

My article on DB2 Basics: Quotation Marks has 962 views so far this year, so I thought it might be time to write a similar article on using quotation marks with MySQL.

General quotation marks

For the most part, you can use single and double quotation marks interchangeably in MySQL as long as you maintain consistency. For example, this works:

mysql> select 'this', "that";
+------+------+
| this | that |
+------+------+
| this | that |
+------+------+
1 row in set (0.01 sec)

While this does not:

mysql> select 'this', "that", 'this and that";
    '>

One exception to this is when dealing with hex values. For these, we must use single quotes:

mysql> select x'41';
+--------------+
| x'41'        |
+--------------+
| 0x41         |
+--------------+
1 row in set (0.00 sec)

If we try to use double quotes in this context, it doesn’t work the way we might expect it to:

mysql> select x"41";
ERROR 1054 (42S22): Unknown column 'x' in 'field list'

There is no difference in the interpretation of single vs. double quotes. It is NOT like programing languages that will interpolate variables in double quotes, but pass single quoted strings along exactly.

Compatibility

After 20 years of working with relational databases, I don’t have any illusions that anyone is going to take an application and point it at a different RDBMS and have it work. But it is worth noting that for most databases, the SQL dialects lean towards single quotes for delimiting strings. This makes it useful to design our own skills to lean towards the single quote.

Curly Quotation Marks vs. Straight Quotation Marks

One pet peeve is that MySQL and other relational databases don’t play well with the fancy different starting and ending quote marks that programs like MS Word or Google Docs convert typed quotation marks into the instant after they are typed. In this case, when I went to copy and paste a string like Select ‘string’; from MS Word or Google Docs into a local MySQL command line, the single quotation marks were just stripped out as if they had never been there. This is different than the Db2 behavior that simply throws an error, but no less annoying.

Tick marks

One area that I found particularly surprising coming from Db2 was the use of tick marks around identifiers. The names of schemas, tables, columns, and such should be enclosed in tick marks to prevent conflicts with keywords. This is not required by MySQL unless the identifier is an actual conflict with a reserved word. For example, this works:

mysql> create table `select` (c1 int);
Query OK, 0 rows affected (0.03 sec)

While eliminating the tick marks does not:

mysql> create table select (c1 int);
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 'select (c1 int)' at line 1

This is because select is a reserved word. I’ve seen many developers use tick marks only when strictly needed. The problem is that with each release, MySQL can add reserved words. We found this recently when upgrading a database from 5.7 to 8.0. One of our tables had a column named LAG. This is a reserved word that was introduced in 8.0 (and the LAG function can be quite useful when querying time series data). Suddenly all application queries that accessed this column without using tick marks started to fail. If we default to the use of tick marks for everything, that can protect us from such failures on upgrade.

Summary

Much like my previous article for Db2, I’ve proven that I can write 500 words on the humble quotation mark! Hopefully there’s something helpful here.