When to use single quotes, double quotes, and backticks in MySQL

  • by

Are you struggle with how to properly use the quote sign in MySQL? This tutorial gives you exhausting information about how and when to use single quotes, double quotes, and backticks in MySQL. Let’s dive in and take a look at backticks.

When to use backticks in MySQL

Usually, you should not use backticks, but the can be used for table and column identifiers. For example:

SELECT `actor`.`first_name`, `actor`.`last_name` ORDER BY `actor`.`last_name`

In this example, we select columns first_name and last_name from the table actor. Backticks telling SQL that those are table and column names. But you can also use the same query without backticks and you will get the same results.

SELECT actor.first_name, actor.last_name ORDER BY actor.last_name

Using backticks is only necessary when the identifier contains whitespace characters, characters beyond the default character set, or identifier is a MySQL reserved keyword. But you should be careful and avoid using reserved keywords as identifiers.

When to use single quotes in MySQL

Use single quotes to enclose string literals. Double quotes are also allowed for enclosing string literals but have some disadvantages.

According to MySQL documentation, date literals(like DATE and DATETIME values) should be single-quoted as strings like '2020-01-03 00:00:00'.

Native RDBMS functions such as NOW() in MySQL should not be quoted, but their arguments are subject to the same string or identifier quoting rules already mentioned.

When to use double quotes

Double quotes are special case. Depending on sql_mode of MySQL server you can use double quotes to:

  1. Enclose string literals just like single quote
  2. To enclose identifiers just like backticks in ANSI_QUOTES mode

Dependent on sql_mode following query will generate different results:

SELECT "first_name" from author where first_name = "Joe"

If ANSI_QUOTES are enabled, this query will select the column first_name where column first_name is equal to column Joe.

If ANSI_QUOTES are disabled, this query will select the string literal first_name where column first_name is equal to string “Joe”.

So if you want to have your code sql_mode independent, you should avoid using double quotes.


Download dbHarbor for free to try SQL queries from this tutorial. ⚓️  dbHarbor is a native macOS tool with intuitive UI to manage SQLite databases.

dbHarbor: SQLite

Don’t miss the latest news about our development journey and SQL tutorials. Signup for our newsletter. Promise not to SPAM😎

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *