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:
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.
Contact Us