Single Quote, Double Quote, and Backticks in MySQL Queries

Single quotes, double quotes, and backticks are used for specific purposes in MySQL queries. They are mainly used to handle string literals and identifiers.

This guide explains how to use single quotes, double quotes, and backticks in MySQL with syntax and examples.

Demo MySQL Database

For this tutorial on Single Quote, Double Quote, and Backticks in MySQL we will use the following table in examples

id	name	age
1	Alice	20
2	Bob	22
3	Charlie	21

To create this table, write the following MySQL queries:

MySQL

CREATE TABLE students 
(
  id INT,
  name VARCHAR(50),
  age INT
);

INSERT INTO students VALUES
  (1, 'Alice', 20),
  (2, 'Bob', 22),
  (3, 'Charlie', 21);

Single Quotes

• Single quotes are used to handle string literals when writing queries. It is denoted by ( ' ' ).
• String values in queries are enclosed between these single quotes.
• This helps the server understand that the enclosed characters are from a string.

Syntax

' String Literal '

MySQL Single Quotes Example

Let's fetch the information about the person whose name is Alice.

SELECT * FROM students WHERE name = 'Alice';

Output:

id	name	age
1	Alice	20

Explanation: The above Query fetches all data from the student table where the person's name is "Alice".

Double Quotes

• Double quotes are also used to define string literals. It is denoted by ( " " ).
• When 'ANSI_QUOTES' mode is enabled, the strings in double quotes are interpreted as identifiers.
• Identifiers identify database objects, such as table or column names.

Syntax

" Identifier "

MySQL Double Quotes Example

Suppose we want to fetch names that are written with double quotes like "Alice", not single quotes.

SET sql_mode = 'ANSI_QUOTES';

SELECT * FROM "students" WHERE "name" = 'Alice';

Output:

id	name	age
1	Alice	20

Explanation: We have successfully fetched the output along with the help of Double Quotes easily.

Backticks

• Backticks are used to quote identifiers such as database names, table names, and column names. It is denoted by (``).
• Backticks ensure the identifiers are correctly identified even though they coincide with the MySQL keywords.
• Backticks are especially required if the database/table/column names contain whitespace characters.

Syntax

`Identifier`

MySQL Backticks Example

Let's fetch the person whose name is Alice using Backticks.

SELECT * FROM `students` WHERE `name` = 'Alice';

Output:

id	name	age
1	Alice	20

Explanation: We have successfully fetched the output along with the help of Backticks easily.

Conclusion

Single quotes are used for string literals. Double quotes are used for identifiers when the 'ANSI_QUOTES' mode is set and for strings in general. Backticks are used for identifiers especially when they are MySQL keywords or contain special characters.

The article explained single quotes, double quotes, and backticks in MySQL with examples. It is important to properly quote and escape your strings and identifiers to prevent SQL syntax errors and SQL injection attacks.