Writing Strings That Include Quotes or Special Characters
Problem
You want to write a quoted string, but it contains quote characters or other special characters, and MySQL rejects it.
Solution
Learn the syntax rules that govern the interpretation of strings in queries.
Discussion
To write a string in a SQL statement, surround it with quote characters:
mysql> SELECT 'hello, world';
+--------------+
| hello, world |
+--------------+
| hello, world |
+--------------+But sometimes you need to write a string that includes a quote character, and if you just put the quote into the string as is, a syntax error results:
mysql> SELECT 'I'm asleep';
ERROR 1064 at line 1: You have an error in your SQL syntax near 'asleep''
at line 1You can deal with this several ways:
MySQL, unlike some SQL engines, allows you to quote strings with either single quotes or double quotes, so you can enclose a string containing single quotes within double quotes:
mysql>
SELECT "I'm asleep";+------------+ | I'm asleep | +------------+ | I'm asleep | +------------+This works in reverse, too; a string containing double quotes can be enclosed within single quotes:
mysql>
SELECT 'He said, "Boo!"';+-----------------+ | He said, "Boo!" | +-----------------+ | He said, "Boo!" | +-----------------+To include a quote character within a string that is quoted by the same kind of quote, either double the quote or precede it with a backslash. When MySQL reads the query string, it will strip off the extra quote or the backslash: ...