MySQL supports single quotes, double quotes and backticks for various purposes. 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
It is important to remember:
- Single quotes and double quotes are used around strings
- Backticks are used around database names, tables names and column names
Single Quotes
Here are some examples of using single quotes in MySQL.
mysql> SELECT 'test' example1,
'"test"' example2,
'""test""' example3,
'te''st' example4;
+----------+----------+----------+----------+
| example1 | example2 | example3 | example4 |
+----------+----------+----------+----------+
| test | "test" | ""test"" | te'st |
+----------+----------+----------+----------+
As you can see above, if you add double quotes inside single quotes, they are treated as string characters and displayed as-is. So if you want to display double quotes in your result, wrap them inside 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 single quotes using backslash (\) as shown below.
mysql> select 'They\'ve gone' as example; +--------------+ | example | +--------------+ | They've gone | +--------------+
Double Quotes
Here are some examples of using double quotes in MySQL.
mysql> SELECT "test" example1,
"'test'" example2,
"''test''" example3,
"te""st" example4;
+----------+----------+----------+----------+
| example1 | example2 | example3 | example4 |
+----------+----------+----------+----------+
| test | 'test' | ''test'' | te"st |
+----------+----------+----------+----------+
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.
If you need to display single quotes in a string and also wrap them in single quotes, then you need to escape the inner single quotes using backslash (\) as shown below.
mysql> select "He said, \"They've gone\"" as example; +-------------------------+ | example | +-------------------------+ | He said, "They've gone" | +-------------------------+
Backticks
Backticks are used around database name, table name, column name. They are required especially if your database/table/column names contain whitespace characters.
Here is an example. We will try 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 mysql> create database `sample data`; Query OK, 1 row affected (0.01 sec) 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.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
