Single quotes, Double quotes and Backticks in MySQL

Last updated on June 6th, 2024 at 06:56 am

MySQL supports numerous ways to specify string literals and identifiers in your SQL queries. It features single quotes, double quotes and backticks that are suitable for specific purposes. They allow you to deftly handle all kinds of strings and identifiers. However, it can be confusing to determine which quotes to use for your use case. In this article, we will look at when to use single quotes, double quotes and backticks in MySQL.

Single quotes, Double quotes and Backticks in MySQL

Let us learn how to use single quotes, double quotes and backticks in MySQL.

1. Single Quotes

Single quotes are used for string literals. They are specified by enclosing your string within single quotation marks. Here is its syntax.

'string'

Please note:

1. Using single quotes is the SQL standard for enclosing strings. It is supported by not only MySQL but also other databases such as PostgreSQL, SQL Server and Oracle. Therefore, if you use single quotes in you SQL query, you can easily migrate it to other databases later on.
2. If you are storing values in Date and Datetime columns, then they are to be enclosed within single quotes.

Here are some examples of using single quotes in MySQL.

As Column Values

In the following example, we use single quotes to specify string literals as column values.

mysql> select 'test' as column_name;
+-------------+
| column_name |
+-------------+
| test        |
+-------------+

In Where Clause

You can also use it in WHERE clause to specify column values to be used for filtering rows.

mysql> SELECT * FROM students WHERE name = 'John';
 +----+-------+-----+
 | id |  name | age |
 +----------+-------+
 | 10 | "John"| 20  |
 +----+-------+-----+

With Double Quotes

In this example, we use single quotes to enclose a string that contains double quotes. You will see that in this case, double quotes are treated as string characters and displayed as-it-is.

mysql> SELECT '"test"' example1, 
              '""test""' example2
 +----------+----------+
 | example1 | example2 |
 +----------+----------+
 | "test"   | ""test"" |
 +----------+----------+

So if you want to display double quotes in your result, wrap them inside single quotes.

With Single Quotes

If you need to display single quotes in a string and also wrap them in single quotes, then you need to escape the inner quotes using backslash (\) as shown below.

mysql> select 'They\'ve gone' as example;
 +--------------+
 | example      |
 +--------------+
 | They've gone |
 +--------------+

2. Double Quotes

Just like single quotes, double quotes are also used for string literals. They are also used by enclosing the desired string within double quotes. Here is its syntax.

"string"

It is important to note that the result of using double quotes depends on the SQL mode of your database. When SQL mode is set to ANSI_QUOTES, MySQL will treat the string in double quotes, as identifiers. Identifiers are names of databases, tables and columns. Otherwise, it treats the string as a string literal. By default, sql_mode flag of database is null.

Here are some examples of using double quotes in MySQL.

As String Literal in Column Values

In this example, the string test is treated as literal.

mysql> select "test" as column_name;
+-------------+
| column_name |
+-------------+
| test        |
+-------------+

As Column Names

Here is an example where we set sql_mode to ANSI_QUOTES. Please note, the column name and table name identifiers are enclosed within double quotes but the literal string John in WHERE clause is enclosed in single quotes. Otherwise, the literal string will be treated as an identifier.

SET sql_mode = 'ANSI_QUOTES';
SELECT * FROM "students" WHERE "name" = 'John';
 +----+-------+-----+
 | id |  name | age |
 +----------+-------+
 | 10 | "John"| 20  |
 +----+-------+-----+

In Where clause

In this example, we have enclosed string literal in double quotes and used it in WHERE clause to filter values. Here the sql_mode is null.

mysql> SELECT * FROM students WHERE name = "John";
 +----+-------+-----+
 | id |  name | age |
 +----------+-------+
 | 10 | "John"| 20  |
 +----+-------+-----+

With Single Quotes

In this example, we use double quotes to enclose a string that contains single quote. This is an excellent way to wrap a text containing contractions such as “They’ve”, as shown below.

mysql> select "They've won" as column_name;
+-------------+
| column_name |
+-------------+
| They've won |
+-------------+

As you can see above, if you add single quotes inside double quotes, they are treated as string characters and displayed as-is. So if you want to display single quotes in your result, wrap them inside double quotes.

With Double Quotes

If you need to display double quotes in a string and also wrap them in double quotes, then you need to escape the inner double quotes using backslash (\) as shown below.

mysql> select "He said, \"They've gone\"" as example;
 +-------------------------+
 | example                 |
 +-------------------------+
 | He said, "They've gone" |
 +-------------------------+

3. Backticks

Backticks are used with identifiers such as database names, tables names and column names. They are required only if your database/table/column names contain whitespace characters or reserved keywords.

Here is an example. We will try to create a database titled “sample data”.

mysql> create database sample data;
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 'data' at line 1

As you can see the above command resulted in error. Here is the correct way to do this using backticks.

mysql> create database `sample data`;
Query OK, 1 row affected (0.01 sec)

Here is another example to illustrate it.

mysql> use `sample data`
ERROR 1049 (42000): Unknown database 'sample';

mysql> use `sample data`
Database changed

Backticks are optional if your database, table and column names do not contain whitespace characters.

But if you are programmatically generating your SQL queries, and using variables to hold identifier values such as table & column names, then it is good to wrap them within backticks. Since we do not know if the identifiers will contain spaces or keywords, it is better to wrap them in backticks.

Conclusion

In this article, we have learnt when & how to use single quotes, double quotes and backticks in MySQL. It is advisable to use single quotes over double quotes, whenever possible, because it is defined by SQL standard. Since single quotes are supported by all major databases such as MSSQL and Oracle, you can easily migrate your queries. Also, the result of using double quotes depends on SQL mode. Using single quotes does not pose this problem.

You can use single quotes within double quotes and vice versa. In this case, the inner quotes will be treated as strings. But if you need to use single quotes within single quotes or double quotes within double quotes, you need to escape the inner quotes using a backslash.

Backticks are used to enclose identifiers such as database, table & column names. They are optional but required if your identifiers contain whitespace or reserved keywords. They are recommended if you are generating SQL queries programmatically with variables to hold identifiers.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!