Chapter 9. Language Structure

MySQL 5.0

Chapter 9. Language Structure

This chapter discusses the rules for writing the following elements of SQL statements when using MySQL:

  • Literal values such as strings and numbers

  • Identifiers such as database, table, and column names

  • User-defined and system variables

  • Comments

  • Reserved words

9.1. Literal Values

This section describes how to write literal values in MySQL. These include strings, numbers, hexadecimal values, boolean values, and . The section also covers the various nuances and “gotchas” that you may run into when dealing with these basic types in MySQL.

9.1.1. Strings

A string is a sequence of bytes or characters, enclosed within either single quote (‘’) or double quote (‘’) characters. Examples:

'a string'
"another string"

If the SQL mode is enabled, string literals can be quoted only within single quotes because a string quoted within double quotes is interpreted as an identifier.

A binary string is a string of bytes that has no character set or collation. A non-binary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit. For binary strings, the unit is the byte. For non-binary strings the unit is the character and some character sets allow multi-byte characters. Character value ordering is a function of the string collation.

String literals may have an optional character set introducer and clause:

[_]'' [COLLATE ]

Examples:

SELECT _latin1'';
SELECT _latin1'' COLLATE latin1_danish_ci;

For more information about these forms of string syntax, see Section 10.3.5, “Character String Literal Character Set and Collation”.

Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (‘’), known as the escape character. MySQL recognizes the following escape sequences:

An ASCII 0 () character.
A single quote (‘’) character.
A double quote (‘’) character.
A backspace character.
A newline (linefeed) character.
A carriage return character.
A tab character.
ASCII 26 (Control-Z). See note following the table.
A backslash (‘’) character.
A ‘’ character. See note following the table.
A ‘’ character. See note following the table.

For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, ‘’ is just ‘’.

These sequences are case sensitive. For example, ‘’ is interpreted as a backspace, but ‘’ is interpreted as ‘’.

The ASCII 26 character can be encoded as ‘’ to enable you to work around the problem that ASCII 26 stands for END-OF-FILE on Windows. ASCII 26 within a file causes problems if you try to use < .

The ‘’ and ‘’ sequences are used to search for literal instances of ‘’ and ‘’ in pattern-matching contexts where they would otherwise be interpreted as wildcard characters. See the description of the operator in Section 12.3.1, “String Comparison Functions”. If you use ‘’ or ‘’ in non-pattern-matching contexts, they evaluate to the strings ‘’ and ‘’, not to ‘’ and ‘’.

There are several ways to include quote characters within a string:

  • A ‘’ inside a string quoted with ‘’ may be written as ‘’.

  • A ‘’ inside a string quoted with ‘’ may be written as ‘’.

  • Precede the quote character by an escape character (‘’).

  • A ‘’ inside a string quoted with ‘’ needs no special treatment and need not be doubled or escaped. In the same way, ‘’ inside a string quoted with ‘’ needs no special treatment.

The following statements demonstrate how quoting and escaping work:

mysql> 
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> 
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> 
+--------------------+
| This
Is
Four
Lines |
+--------------------+

mysql> 
+------------------------+
| disappearing backslash |
+------------------------+

If you want to insert binary data into a string column (such as a column), the following characters must be represented by escape sequences:

byte (ASCII 0). Represent this character by ‘’ (a backslash followed by an ASCII ‘’ character).
Backslash (ASCII 92). Represent this character by ‘’.
Single quote (ASCII 39). Represent this character by ‘’.
Double quote (ASCII 34). Represent this character by ‘’.

When writing application programs, any string that might contain any of these special characters must be properly escaped before the string is used as a data value in an SQL statement that is sent to the MySQL server. You can do this in two ways:

  • Process the string with a function that escapes the special characters. In a C program, you can use the C API function to escape characters. See Section 22.2.3.52, “. The Perl DBI interface provides a method to convert special characters to the proper escape sequences. See Section 22.4, “MySQL Perl API”. Other language interfaces may provide a similar capability.

  • As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that enables you to insert special markers into a statement string, and then bind data values to them when you issue the statement. In this case, the API takes care of escaping special characters in the values for you.

9.1.2. Numbers

Integers are represented as a sequence of digits. Floats use ‘’ as a decimal separator. Either type of number may be preceded by ‘’ or ‘’ to indicate a negative or positive value, respectively

Examples of valid integers:

1221
0
-32

Examples of valid floating-point numbers:

294.42
-32032.6809e+10
148.00

An integer may be used in a floating-point context; it is interpreted as the equivalent floating-point number.

9.1.3. Hexadecimal Values

MySQL supports hexadecimal values. In numeric contexts, these act like integers (64-bit precision). In string contexts, these act like binary strings, where each pair of hex digits is converted to a character:

mysql> 
        -> 'MySQL'
mysql> 
        -> 10
mysql> 
        -> 'Paul'

The default type of a hexadecimal value is a string. If you want to ensure that the value is treated as a number, you can use :

mysql> 
        -> 'A', 65

The ' syntax is based on standard SQL. The syntax is based on ODBC. Hexadecimal strings are often used by ODBC to supply values for columns.

You can convert a string or a number to a string in hexadecimal format with the function:

mysql> 
        -> '636174'
mysql> 
        -> 'cat'

9.1.4. Boolean Values

The constants and evaluate to and , respectively. The constant names can be written in any lettercase.

mysql> 
        -> 1, 1, 0, 0

9.1.5. Bit-Field Values

Beginning with MySQL 5.0.3, bit-field values can be written using ' notation. is a binary value written using zeros and ones.

Bit-field notation is convenient for specifying values to be assigned to columns:

mysql> 
mysql> 
mysql> 
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
+------+----------+----------+----------+

9.1.6. NULL Values

The value means “no data. can be written in any lettercase.

Be aware that the value is different from values such as for numeric types or the empty string for string types. See Section A.5.3, “Problems with Values”.

For text file import or export operations performed with or , is represented by the sequence. See Section 13.2.5, “ Syntax”.