Chapter 9 Language Structure

A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters. Examples:

'a string'
"another string"

Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:

'a string'
'a' ' ' 'string'

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

A binary string is a string of bytes that has no character set or collation. A nonbinary 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 nonbinary strings the unit is the character and some character sets support multibyte characters. Character value ordering is a function of the string collation.

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

[_charset_name]'string' [COLLATE collation_name]

Examples:

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

You can use N'literal' (or n'literal') to create a string in the national character set. These statements are equivalent:

SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';

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

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the escape sequences shown in Table 9.1, “Special Character Escape Sequences”. For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, “\x” is just “x”. These sequences are case sensitive. For example, “\b” is interpreted as a backspace, but “\B” is interpreted as “B”. Escape processing is done according to the character set indicated by the character_set_connection system variable. This is true even for strings that are preceded by an introducer that indicates a different character set, as discussed in Section 10.1.4.5, “Character String Literal Character Set and Collation”.

The ASCII 26 character can be encoded as “\Z” 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 mysql db_name < file_name.

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 LIKE operator in Section 12.5.1, “String Comparison Functions”. If you use “\%” or “\_” outside of pattern-matching contexts, they evaluate to the strings “\%” and “\_”, not to “%” and “_”.

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

The following SELECT statements demonstrate how quoting and escaping work:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+

mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+

If you want to insert binary data into a string column (such as a BLOB column), you should represent certain characters by escape sequences. Backslash (“\”) and the quote character used to quote the string must be escaped. In certain client environments, it may also be necessary to escape NUL or Control+Z. The mysql client truncates quoted strings containing NUL characters if they are not escaped, and Control+Z may be taken for END-OF-FILE on Windows if not escaped. For the escape sequences that represent each of these characters, see Table 9.1, “Special Character Escape Sequences”.

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:

Number literals include exact-value (integer and DECIMAL) literals and approximate-value (floating-point) literals.

Integers are represented as a sequence of digits. Numbers may include “.” as a decimal separator. Numbers may be preceded by “-” or “+” to indicate a negative or positive value, respectively. Numbers represented in scientific notation with a mantissa and exponent are approximate-value numbers.

Exact-value numeric literals have an integer part or fractional part, or both. They may be signed. Examples: 1, .2, 3.4, -5, -6.78, +9.10.

Approximate-value numeric literals are represented in scientific notation with a mantissa and exponent. Either or both parts may be signed. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Two numbers that look similar may be treated differently. For example, 2.34 is an exact-value (fixed-point) number, whereas 2.34E0 is an approximate-value (floating-point) number.

The DECIMAL data type is a fixed-point type and calculations are exact. In MySQL, the DECIMAL type has several synonyms: NUMERIC, DEC, FIXED. The integer types also are exact-value types. For more information about exact-value calculations, see Section 12.18, “Precision Math”.

The FLOAT and DOUBLE data types are floating-point types and calculations are approximate. In MySQL, types that are synonymous with FLOAT or DOUBLE are DOUBLE PRECISION and REAL.

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

Date and time values can be represented in several formats, such as quoted strings or as numbers, depending on the exact type of the value and other factors. For example, in contexts where MySQL expects a date, it interprets any of '2015-07-21', '20150721', and 20150721 as a date.

This section describes the acceptable formats for date and time literals. For more information about the temporal data types, such as the range of permitted values, consult these sections:

Standard SQL and ODBC Date and Time Literals.  Standard SQL permits temporal literals to be specified using a type keyword and a string. The space between the keyword and string is optional.

DATE 'str'
TIME 'str'
TIMESTAMP 'str'

MySQL recognizes those constructions and also the corresponding ODBC syntax:

{ d 'str' }
{ t 'str' }
{ ts 'str' }

However, MySQL ignores the type keyword and each of the preceding constructions produces the string value 'str', with a type of VARCHAR.

String and Numeric Literals in Date and Time Context.  MySQL recognizes DATE values in these formats:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into DATETIME or TIMESTAMP columns. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:

See also Section 11.3.8, “Two-Digit Years in Dates”.

For values specified as strings that include date part delimiters, it is unnecessary to specify two digits for month or day values that are less than 10. '2015-6-9' is the same as '2015-06-09'. Similarly, for values specified as strings that include time part delimiters, it is unnecessary to specify two digits for hour, minute, or second values that are less than 10. '2015-10-30 1:2:3' is the same as '2015-10-30 01:02:03'.

Values specified as numbers should be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format and that the year is given by the first 2 digits. Numbers that are not one of these lengths are interpreted as though padded with leading zeros to the closest length.

Values specified as nondelimited strings are interpreted according their length. For a string 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise, the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string. This means you should not use strings that have fewer than 6 characters. For example, if you specify '9903', thinking that represents March, 1999, MySQL converts it to the “zero” date value. This occurs because the year and month values are 99 and 03, but the day part is completely missing. However, you can explicitly specify a value of zero to represent missing month or day parts. For example, to insert the value '1999-03-00', use '990300'.

MySQL recognizes TIME values in these formats:

A trailing fractional seconds part is recognized in the 'D HH:MM:SS.fraction', 'HH:MM:SS.fraction', 'HHMMSS.fraction', and HHMMSS.fraction time formats, where fraction is the fractional part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into TIME columns. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

For TIME values specified as strings that include a time part delimiter, it is unnecessary to specify two digits for hours, minutes, or seconds values that are less than 10. '8:3:2' is the same as '08:03:02'.

MySQL supports hexadecimal values, written using X'val', x'val', or 0xval format, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits does not matter. For values written using X'val' or x'val' format, val must contain an even number of digits. For values written using 0xval syntax, values that contain an odd number of digits are treated as having an extra leading 0. For example, 0x0a and 0xaaa are interpreted as 0x0a and 0x0aaa.

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

mysql> SELECT X'4D7953514C';
        -> 'MySQL'
mysql> SELECT x'0a'+0;
        -> 10
mysql> SELECT 0x5061756c;
        -> '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 CAST(... AS UNSIGNED):

mysql> SELECT X'41', CAST(X'41' AS UNSIGNED);
        -> 'A', 65

The X'hexstring' and x'val' syntaxes are based on standard SQL. The 0x syntax is based on ODBC. Hexadecimal strings are often used by ODBC to supply values for BLOB columns.

To convert a string or a number to a string in hexadecimal format, use the HEX() function:

mysql> SELECT HEX('cat');
        -> '636174'
mysql> SELECT X'636174';
        -> 'cat'

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

mysql> SELECT TRUE, true, FALSE, false;
        -> 1, 1, 0, 0

Bit-field values can be written using b'value' or 0bvalue notation. value is a binary value written using zeros and ones.

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

mysql> CREATE TABLE t (b BIT(8));
mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
mysql> INSERT INTO t SET b = b'0101';

Bit values are returned as binary values. To display them in printable form, add 0 or use a conversion function such as BIN(). High-order 0 bits are not displayed in the converted value.

mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
|    5 | 101      | 5        | 5        |
+------+----------+----------+----------+

Bit values assigned to user variables are treated as binary strings. To assign a bit value as a number to a user variable, use CAST() or +0:

mysql> SET @v1 = 0b1000001;
mysql> SET @v2 = CAST(0b1000001 AS UNSIGNED), @v3 = 0b1000001+0;
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+

The NULL value means “no data.” NULL can be written in any lettercase. A synonym is \N (case sensitive).

For text file import or export operations performed with LOAD DATA INFILE or SELECT ... INTO OUTFILE, NULL is represented by the \N sequence. See Section 13.2.6, “LOAD DATA INFILE Syntax”.

Be aware that the NULL value is different from values such as 0 for numeric types or the empty string for string types. For more information, see Section B.5.4.3, “Problems with NULL Values”.

MySQL permits names that consist of a single identifier or multiple identifiers. The components of a multiple-part name must be separated by period (“.”) characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which the final identifier is interpreted.

In MySQL, you can refer to a table column using any of the following forms.

The qualifier character is a separate token and need not be contiguous with the associated identifiers. For example, tbl_name.col_name and tbl_name . col_name are equivalent.

If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, write `my-table`.`my-column`, not `my-table.my-column`.

A reserved word that follows a period in a qualified name must be an identifier, so in that context it need not be quoted.

You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a statement unless the reference would be ambiguous. Suppose that tables t1 and t2 each contain a column c, and you retrieve c in a SELECT statement that uses both t1 and t2. In this case, c is ambiguous because it is not unique among the tables used in the statement. You must qualify it with a table name as t1.c or t2.c to indicate which table you mean. Similarly, to retrieve from a table t in database db1 and from a table t in database db2 in the same statement, you must refer to columns in those tables as db1.t.col_name and db2.t.col_name.

The syntax .tbl_name means the table tbl_name in the default database. This syntax is accepted for ODBC compatibility because some ODBC programs prefix table names with a “.” character.

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. One notable exception is OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, OS X also supports UFS volumes, which are case sensitive just as on any Unix. See Section 1.7.1, “MySQL Extensions to Standard SQL”. The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.

However, names of logfile groups are case sensitive. This differs from standard SQL.

By default, table aliases are case sensitive on Unix, but not so on Windows or OS X. The following statement would not work on Unix, because it refers to the alias both as a and as A:

mysql> SELECT col_name FROM tbl_name AS a
    -> WHERE a.col_name = 1 OR A.col_name = 2;

However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

How table and database names are stored on disk and used in MySQL is affected by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take the values shown in the following table. This variable does not affect case sensitivity of trigger identifiers. On Unix, the default value of lower_case_table_names is 0. On Windows, the default value is 1. On OS X, the default value is 2.

If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting. To do this for an individual table, use RENAME TABLE:

RENAME TABLE T1 TO t1;

To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:

Object names may be considered duplicates if their uppercase forms are equal according to a binary collation. That is true for names of cursors, conditions, procedures, functions, savepoints, stored routine parameters, stored program local variables, and plugins. It is not true for names of columns, constraints, databases, partitions, statements prepared with PREPARE, tables, triggers, users, and user-defined variables.

File system case sensitivity can affect searches in string columns of INFORMATION_SCHEMA tables. For more information, see Section 10.1.8.8, “Collation and INFORMATION_SCHEMA Searches”.

There is a correspondence between database and table identifiers and names in the file system. For the basic structure, MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory. For the table format files (.FRM), the data is always stored in this structure and location.

For the data and index files, the exact representation on disk is storage engine specific. These files may be stored in the same location as the FRM files, or the information may be stored in a separate file. InnoDB data is stored in the InnoDB data files. If you are using tablespaces with InnoDB, then the specific tablespace files you create are used instead.

Any character is legal in database or table identifiers except ASCII NUL (X'00'). MySQL encodes any characters that are problematic in the corresponding file system objects when it creates database directories or table files:

On Windows, some names such as nul, prn, and aux are encoded by appending @@@ to the name when the server creates the corresponding file or directory. This occurs on all platforms for portability of the corresponding database object between platforms.

If you have databases or tables from a version of MySQL older than 5.1.6 that contain special characters and for which the underlying directory names or file names have not been updated to use the new encoding, the server displays their names with a prefix of #mysql50# in the output from INFORMATION_SCHEMA tables or SHOW statements. For example, if you have a table named a@b and its name encoding has not been updated, SHOW TABLES displays it like this:

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| #mysql50#a@b   |
+----------------+

To refer to such a name for which the encoding has not been updated, you must supply the #mysql50# prefix:

mysql> SHOW COLUMNS FROM `a@b`;
ERROR 1146 (42S02): Table 'test.a@b' doesn't exist

mysql> SHOW COLUMNS FROM `#mysql50#a@b`;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

To update old names to eliminate the need to use the special prefix to refer to them, re-encode them with mysqlcheck. The following commands update all names to the new encoding:

shell> mysqlcheck --check-upgrade --all-databases
shell> mysqlcheck --fix-db-names --fix-table-names --all-databases

To check only specific databases or tables, omit --all-databases and provide the appropriate database or table arguments. For information about mysqlcheck invocation syntax, see Section 4.5.3, “mysqlcheck — A Table Maintenance Program”.

MySQL 5.5 supports built-in (native) functions, user-defined functions (UDFs), and stored functions. This section describes how the server recognizes whether the name of a built-in function is used as a function call or as an identifier, and how the server determines which function to use in cases when functions of different types exist with a given name.

Built-In Function Name Parsing

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode.

When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name. For example, in the following statements, the first reference to count is a function call, whereas the second reference is a table name:

SELECT COUNT(*) FROM mytable;
CREATE TABLE count (i INT);

The parser should recognize the name of a built-in function as indicating a function call only when parsing what is expected to be an expression. That is, in nonexpression context, function names are permitted as identifiers.

However, some built-in functions have special parsing or implementation considerations, so the parser uses the following rules by default to distinguish whether their names are being used as function calls or as identifiers in nonexpression context:

The requirement that function calls be written with no whitespace between the name and the parenthesis applies only to the built-in functions that have special considerations. COUNT is one such name. The sql_functions[] array in the sql/lex.h source file lists the names of these special functions for which following whitespace determines their interpretation. Before MySQL 5.1, these are rather numerous (about 200). In MySQL 5.1 and later, parser improvements reduce to about 30 the number of affected function names. You may find it easiest to treat the no-whitespace requirement as applying to all function calls.

The following table names the functions that are affected by the IGNORE_SPACE setting and listed as special in the sql/lex.h source file. For versions older than MySQL 5.1.13, the list is much longer; check the sql_functions[] array in the sql/lex.h source file.

For functions not listed as special in sql/lex.h, whitespace does not matter. They are interpreted as function calls only when used in expression context and may be used freely as identifiers otherwise. ASCII is one such name. However, for these nonaffected function names, interpretation may vary in expression context: func_name () is interpreted as a built-in function if there is one with the given name; if not, func_name () is interpreted as a user-defined function or stored function if one exists with that name.

The IGNORE_SPACE SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive:

To enable the IGNORE_SPACE SQL mode, use this statement:

SET sql_mode = 'IGNORE_SPACE';

IGNORE_SPACE is also enabled by certain other composite modes such as ANSI that include it in their value:

SET sql_mode = 'ANSI';

Check Section 5.1.7, “Server SQL Modes”, to see which composite modes enable IGNORE_SPACE.

To minimize the dependency of SQL code on the IGNORE_SPACE setting, use these guidelines:

Incompatibility warning: The change in MySQL 5.1.13 that reduces the number of function names affected by IGNORE_SPACE improves the consistency of parser operation. However, it also introduces the possibility of incompatibility for old SQL code that relies on the following conditions:

For functions that are no longer affected by IGNORE_SPACE as of MySQL 5.1.13, that strategy no longer works. Either of the following approaches can be used if you have code that is subject to the preceding incompatibility:

Function Name Resolution

The following rules describe how the server resolves references to function names for function creation and invocation:

The preceding function name resolution rules have implications for upgrading to versions of MySQL that implement new built-in functions: