MySQL Cookbook, 2nd Edition

Writing String Literals

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 Handling Special Characters in Identifiers.) 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 ...