Connections, Character Sets and Strings - 5.4 Writing String Literals
(Page 2 of 4 )
Problem
You need to write literal strings in SQL statements.
Solution
Learn the syntax rules that govern string values.
Discussion
Strings can be written several ways:
Enclose the text of the string within single or double quotes:
'my string' "my string"
Be aware that you cannot use double quotes for quoting strings when the ANSI_QUOTES SQL mode is enabled. With that mode enabled, the server interprets double quote as the quoting character for identifiers such as table or column names, and not for strings. (See Recipe 2.6.) For this reason, it’s preferable to adopt the convention of always writing quoted strings using single quotes. That way, MySQL will interpret them as strings and not as identifiers regardless of the ANSI_QUOTES setting.
Use hexadecimal notation. Each pair of hex digits produces one byte of the string. abcd can be written using any of these formats:
0x61626364 X'61626364' x'61626364'
MySQL treats strings written using hex notation as binary strings. Not coincidentally, it’s common for applications to use hex strings when constructing SQL statements that refer to binary values:
INSERT INTO t SET binary_col = 0xdeadbeef;
To specify a character set for interpretation of a literal string, use an introducer consisting of a character set name preceded by an underscore:
_latin1 'abcd' _ucs2 'abcd'
An introducer tells the server how to interpret the following string. For _latin1 'abcd', the server produces a string consisting of four single-byte characters. For _ucs2 'abcd', the server produces a string consisting of two two-byte characters because ucs2 is a double-byte character set.
To include a quote character within a string that is quoted by the same kind of quote, either double the quote or precede it by a backslash. When MySQL reads the statement, it will strip off the extra quote or the backslash:
mysql> SELECT "He said, ""Boo!""", "And I said, "Yikes!"";
He said, "Boo!" And I said, "Yikes!"
He said, "Boo!" And I said, "Yikes!"
A backslash turns off any special meaning of the following character. (It causes a temporary escape from normal string processing rules, so sequences such as \' and " are called escape sequences.) This means that backslash itself is special. To write a literal backslash within a string, you must double it:
mysql> SELECT 'Install MySQL in C:\\mysql on Windows';
Install MySQL in C:\mysql on Windows
Install MySQL in C:\mysql on Windows
Other escape sequences recognized by MySQL are \b (backspace), \n (newline, also called linefeed), \r (carriage return), \t (tab), and \0 (ASCII NUL).
If you are issuing SQL statements from within a program, you can refer to strings or binary values symbolically and let your programming interface take care of quoting: use the placeholder mechanism provided by the language’s database-access API. See the LOAD_FILE( )