Single quote, double quote, and backticks in MySQL queries
Posted by: Tim Miller
Using Backticks, Double Quotes, and Single Quotes when querying a MySQL database can be boiled down to two basic points.
- Quotes (Single and Double) are used around strings.
- Backticks are used around table and column identifiers.
Double quotes
Using double quotes here is some input and output examples:
SELECT "test", "'test'", "''test''", "te""st";The output looks like this:
Wrapping single quotes inside of double quotes will cancel out the expected behavior of the single quotes in the MySQL Query and instead treat it as part of the string. This can be seen in columns 2 and 3 in the example above.
Inserting two double quotes in the middle of the string will cancel out one of them.
Single quotes
Using single quotes here is some input and output examples:
SELECT 'test', '"test"', '""test""', 'te''st';The output looks like this:
As shown in the demonstration above, single quotes behave the same way as double quotes in these contexts.
Using single quotes and double quotes together
Often times there will be a contraction in a string, or a direct quote. In situations like in NPS survey reports or other customer feedback forms this is often the case. In these cases using double quotes to wrap a text string that contains a contraction like They’ve will keep the single quote in the string as an apostrophe.
In this case presenting a string with a contraction should look like this:
SELECT "They've found this tutorial to be helpful"The output looks like this:
Or, if you need to use double quotes to present a customer feedback quote in the string, you can use single quotes to wrap the whole string.
SELECT 'They responded, "We found this tutorial helpful"'If you need to use single quotes and double quotes in a string that contains both a contraction and a quote, you will need to use the backslash ‘' to cancel out the following character. For example: a string containing this ' will recognize the backslash as an instruction to cancel out the single quote’s syntactical meaning and instead insert it into the string as an apostrophe.
SELECT 'They\'ve responded, "We found this tutorial helpful"'
Backticks
Backticks are used in MySQL to select columns and tables from your MySQL source. In the example below we are calling to the table titled Album and the column Title. Using backticks we are signifying that those are the column and table names.
SELECT `Album`.`Title`
FROM `Album` AS `Album`
GROUP BY `Album`.`Title`
ORDER BY `Title` ASC
LIMIT 10;The backticks for column names may not be necessary though.
SELECT Album.Title
FROM Album AS Album
GROUP BY Album.Title
ORDER BY Title ASC
LIMIT 10;Both of these queries will return the same result.
Putting it all together
The following query will use all we’ve learned here, including double quotes, single quotes, and backticks.
SELECT 'They\'ve responded, "We found this tutorial helpful"' as `Response`Will return:
