9.1 Literal Values

This section describes how to write literal values in MySQL. These include strings, numbers, hexadecimal and bit values, boolean values, and 'NULL'. The section also covers various nuances that you may encounter when dealing with these basic types in MySQL.

 File: manual.info.tmp, Node: string-literals, Next: number-literals, Prev: literals, Up: literals

9.1.1 String Literals

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. Every binary string has a character set and collation named 'binary'. A nonbinary string is a string of characters. It has a character set other than 'binary' and a collation that is compatible with the character set.

For both types of strings, comparisons are based on the numeric values of the string unit. For binary strings, the unit is the byte; comparisons use numeric byte values. For nonbinary strings, the unit is the character and some character sets support multibyte characters; comparisons use numeric character code values. Character code ordering is a function of the string collation. (For more information, see *note charset-binary-collations::.)

Note:

Within the note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.

A character string literal may have an optional character set introducer and 'COLLATE' clause, to designate it as a string that uses a particular character set and collation:

 [_CHARSET_NAME]'STRING' [COLLATE COLLATION_NAME]

Examples:

 SELECT _latin1'STRING';
 SELECT _binary'STRING';
 SELECT _utf8'STRING' COLLATE utf8_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 information about these forms of string syntax, see note charset-national::, and note charset-introducer::.

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 note 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, '' is just 'x'. These sequences are case-sensitive. For example, '' is interpreted as a backspace, but '' 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 note charset-literal::.

Special Character Escape Sequences

Escape Character Represented by Sequence Sequence

'' An ASCII NUL ('X'00'') 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

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 '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 *note 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 *note 'SELECT': 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 |
 +------------------------+

To insert binary data into a string column (such as a note 'BLOB': 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 note 'mysql': 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 *note 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:

 File: manual.info.tmp, Node: number-literals, Next: date-and-time-literals, Prev: string-literals, Up: literals

9.1.2 Numeric Literals

Number literals include exact-value (integer and *note 'DECIMAL': fixed-point-types.) 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 note 'DECIMAL': fixed-point-types. data type is a fixed-point type and calculations are exact. In MySQL, the note 'DECIMAL': fixed-point-types. type has several synonyms: note 'NUMERIC': fixed-point-types, note 'DEC': fixed-point-types, note 'FIXED': fixed-point-types. The integer types also are exact-value types. For more information about exact-value calculations, see note precision-math::.

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

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

 File: manual.info.tmp, Node: date-and-time-literals, Next: hexadecimal-literals, Prev: number-literals, Up: literals

9.1.3 Date and Time Literals

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, see *note date-and-time-types::.

Standard SQL and ODBC Date and Time Literals

Standard SQL requires 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 but, unlike standard SQL, does not require the type keyword. Applications that are to be standard-compliant should include the type keyword for temporal literals.

MySQL also recognizes the ODBC syntax corresponding to the standard SQL syntax:

 { d 'STR' }
 { t 'STR' }
 { ts 'STR' }

MySQL uses the type keywords and the ODBC constructions to produce note 'DATE': datetime, note 'TIME': time, and note 'DATETIME': datetime. values, respectively, including a trailing fractional seconds part if specified. The note 'TIMESTAMP': datetime. syntax produces a note 'DATETIME': datetime. value in MySQL because note 'DATETIME': datetime. has a range that more closely corresponds to the standard SQL note 'TIMESTAMP': datetime. type, which has a year range from '0001' to '9999'. (The MySQL note 'TIMESTAMP': datetime. year range is '1970' to '2038'.)

String and Numeric Literals in Date and Time Context

MySQL recognizes *note 'DATE': datetime. values in these formats:

MySQL recognizes note 'DATETIME': datetime. and note 'TIMESTAMP': datetime. values in these formats:

A note 'DATETIME': datetime. or note 'TIMESTAMP': datetime. value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see *note fractional-seconds::.

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

See also *note two-digit-years::.

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 *note 'TIME': 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. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see *note fractional-seconds::.

For *note 'TIME': 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''.

 File: manual.info.tmp, Node: hexadecimal-literals, Next: bit-value-literals, Prev: date-and-time-literals, Up: literals

9.1.4 Hexadecimal Literals

Hexadecimal literal values are written using 'X'VAL'' or '0xVAL' notation, where VAL contains hexadecimal digits ('0..9', 'A..F'). Lettercase of the digits and of any leading 'X' does not matter. A leading '0x' is case-sensitive and cannot be written as '0X'.

Legal hexadecimal literals:

 X'01AF'
 X'01af'
 x'01AF'
 x'01af'
 0x01AF
 0x01af

Illegal hexadecimal literals:

 X'0G'   (G is not a hexadecimal digit)
 0X01AF  (0X must be written as 0x)

Values written using 'X'VAL'' notation must contain an even number of digits or a syntax error occurs. To correct the problem, pad the value with a leading zero:

 mysql> SET @s = X'FFF';
 ERROR 1064 (42000): You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server
 version for the right syntax to use near 'X'FFF''

 mysql> SET @s = X'0FFF';
 Query OK, 0 rows affected (0.00 sec)

Values written using '0xVAL' notation that contain an odd number of digits are treated as having an extra leading '0'. For example, '0xaaa' is interpreted as '0x0aaa'.

By default, a hexadecimal literal is a binary string, where each pair of hexadecimal digits represents a character:

 mysql> SELECT X'4D7953514C', CHARSET(X'4D7953514C');
 +---------------+------------------------+
 | X'4D7953514C' | CHARSET(X'4D7953514C') |
 +---------------+------------------------+
 | MySQL         | binary                 |
 +---------------+------------------------+
 mysql> SELECT 0x5461626c65, CHARSET(0x5461626c65);
 +--------------+-----------------------+
 | 0x5461626c65 | CHARSET(0x5461626c65) |
 +--------------+-----------------------+
 | Table        | binary                |
 +--------------+-----------------------+

A hexadecimal literal may have an optional character set introducer and 'COLLATE' clause, to designate it as a string that uses a particular character set and collation:

 [_CHARSET_NAME] X'VAL' [COLLATE COLLATION_NAME]

Examples:

 SELECT _latin1 X'4D7953514C';
 SELECT _utf8 0x4D7953514C COLLATE utf8_danish_ci;

The examples use 'X'VAL'' notation, but '0xVAL' notation permits introducers as well. For information about introducers, see *note charset-introducer::.

In numeric contexts, MySQL treats a hexadecimal literal like a 'BIGINT UNSIGNED' (64-bit unsigned integer). To ensure numeric treatment of a hexadecimal literal, use it in numeric context. Ways to do this include adding 0 or using 'CAST(... AS UNSIGNED)'. For example, a hexadecimal literal assigned to a user-defined variable is a binary string by default. To assign the value as a number, use it in numeric context:

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

An empty hexadecimal value ('X''') evaluates to a zero-length binary string. Converted to a number, it produces 0:

 mysql> SELECT CHARSET(X''), LENGTH(X'');
 +--------------+-------------+
 | CHARSET(X'') | LENGTH(X'') |
 +--------------+-------------+
 | binary       |           0 |
 +--------------+-------------+
 mysql> SELECT X''+0;
 +-------+
 | X''+0 |
 +-------+
 |     0 |
 +-------+

The 'X'VAL'' notation is based on standard SQL. The '0x' notation is based on ODBC, for which hexadecimal strings are often used to supply values for *note 'BLOB': blob. columns.

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

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

 File: manual.info.tmp, Node: bit-value-literals, Next: boolean-literals, Prev: hexadecimal-literals, Up: literals

9.1.5 Bit-Value Literals

Bit-value literals are written using 'b'VAL'' or '0bVAL' notation. VAL is a binary value written using zeros and ones. Lettercase of any leading 'b' does not matter. A leading '0b' is case-sensitive and cannot be written as '0B'.

Legal bit-value literals:

 b'01'
 B'01'
 0b01

Illegal bit-value literals:

 b'2'    (2 is not a binary digit)
 0B01    (0B must be written as 0b)

By default, a bit-value literal is a binary string:

 mysql> SELECT b'1000001', CHARSET(b'1000001');
 +------------+---------------------+
 | b'1000001' | CHARSET(b'1000001') |
 +------------+---------------------+
 | A          | binary              |
 +------------+---------------------+
 mysql> SELECT 0b1100001, CHARSET(0b1100001);
 +-----------+--------------------+
 | 0b1100001 | CHARSET(0b1100001) |
 +-----------+--------------------+
 | a         | binary             |
 +-----------+--------------------+

A bit-value literal may have an optional character set introducer and 'COLLATE' clause, to designate it as a string that uses a particular character set and collation:

 [_CHARSET_NAME] b'VAL' [COLLATE COLLATION_NAME]

Examples:

 SELECT _latin1 b'1000001';
 SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;

The examples use 'b'VAL'' notation, but '0bVAL' notation permits introducers as well. For information about introducers, see *note charset-introducer::.

In numeric contexts, MySQL treats a bit literal like an integer. To ensure numeric treatment of a bit literal, use it in numeric context. Ways to do this include adding 0 or using 'CAST(... AS UNSIGNED)'. For example, a bit literal assigned to a user-defined variable is a binary string by default. To assign the value as a number, use it in numeric context:

 mysql> SET @v1 = b'1100001';
 mysql> SET @v2 = b'1100001'+0;
 mysql> SET @v3 = CAST(b'1100001' AS UNSIGNED);
 mysql> SELECT @v1, @v2, @v3;
 +------+------+------+
 | @v1  | @v2  | @v3  |
 +------+------+------+
 | a    |   97 |   97 |
 +------+------+------+

An empty bit value ('b''') evaluates to a zero-length binary string. Converted to a number, it produces 0:

 mysql> SELECT CHARSET(b''), LENGTH(b'');
 +--------------+-------------+
 | CHARSET(b'') | LENGTH(b'') |
 +--------------+-------------+
 | binary       |           0 |
 +--------------+-------------+
 mysql> SELECT b''+0;
 +-------+
 | b''+0 |
 +-------+
 |     0 |
 +-------+

Bit-value notation is convenient for specifying values to be assigned to *note 'BIT': bit-type. 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 in result sets are returned as binary values, which may not display well. To convert a bit value to printable form, use it in numeric context or use a conversion function such as 'BIN()' or 'HEX()'. High-order 0 digits are not displayed in the converted value.

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

 File: manual.info.tmp, Node: boolean-literals, Next: null-values, Prev: bit-value-literals, Up: literals

9.1.6 Boolean Literals

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

 File: manual.info.tmp, Node: null-values, Prev: boolean-literals, Up: literals

9.1.7 NULL Values

The 'NULL' value means 'no data.' 'NULL' can be written in any lettercase. A synonym is '' (case-sensitive). Treatment of '' as a synonym for 'NULL' in SQL statements is deprecated as of MySQL 5.7.18 and is removed in MySQL 8.0; use 'NULL' instead.

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 *note problems-with-null::.

For text file import or export operations performed with note 'LOAD DATA': load-data. or note 'SELECT ... INTO OUTFILE': select-into, 'NULL' is represented by the '' sequence. See *note load-data::. Use of '' in text files is unaffected by the deprecation of '' in SQL statements.

For sorting with 'ORDER BY', 'NULL' values sort before other values for ascending sorts, after other values for descending sorts.

 File: manual.info.tmp, Node: identifiers, Next: keywords, Prev: literals, Up: language-structure