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.

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
Section 10.1.8.5, “The binary Collation Compared to _bin Collations”.)

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:

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.3.6, “Character String Literal Character Set and Collation”.

Table 9.1 Special Character Escape Sequences

Escape Sequence

Character Represented by Sequence

\0

An ASCII NUL (X'00') character

\'

A single quote (') character

\"

A double quote (") character

\b

A backspace character

\n

A newline (linefeed) character

\r

A carriage return character

\t

A tab character

\Z

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 \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:

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 SELECT statements
demonstrate how quoting and escaping work:

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:

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 Numeric Literals

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.

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

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, 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' }

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

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

As a string in either 'YYYY-MM-DD' or
'YY-MM-DD' format. A
“relaxed” syntax is permitted: Any punctuation
character may be used as the delimiter between date parts.
For example, '2012-12-31',
'2012/12/31',
'2012^12^31', and
'2012@12@31' are equivalent.

As a string with no delimiters in either
'YYYYMMDD' or 'YYMMDD'
format, provided that the string makes sense as a date. For
example, '20070523' and
'070523' are interpreted as
'2007-05-23', but
'071332' is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'.

As a number in either YYYYMMDD or
YYMMDD format, provided that the number
makes sense as a date. For example,
19830905 and 830905
are interpreted as '1983-09-05'.

As a string in either 'YYYY-MM-DD
HH:MM:SS' or 'YY-MM-DD
HH:MM:SS' format. A “relaxed” syntax
is permitted here, too: Any punctuation character may be
used as the delimiter between date parts or time parts. For
example, '2012-12-31 11:30:45',
'2012^12^31 11+30+45',
'2012/12/31 11*30*45', and
'2012@12@31 11^30^45' are equivalent.

The only delimiter recognized between a date and time part
and a fractional seconds part is the decimal point.

The date and time parts can be separated by
T rather than a space. For example,
'2012-12-31 11:30:45''2012-12-31T11:30:45' are equivalent.

As a string with no delimiters in either
'YYYYMMDDHHMMSS' or
'YYMMDDHHMMSS' format, provided that the
string makes sense as a date. For example,
'20070523091528' and
'070523091528' are interpreted as
'2007-05-23 09:15:28', but
'071122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00
00:00:00'.

As a number in either YYYYMMDDHHMMSS or
YYMMDDHHMMSS format, provided that the
number makes sense as a date. For example,
19830905132800 and
830905132800 are interpreted as
'1983-09-05 13:28:00'.

A DATETIME or
TIMESTAMP 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
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:

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'.

As a string in 'D HH:MM:SS' format. You
can also use one of the following “relaxed”
syntaxes: 'HH:MM:SS',
'HH:MM', 'D HH:MM',
'D HH', or 'SS'. Here
D represents days and can have a value
from 0 to 34.

As a string with no delimiters in
'HHMMSS' format, provided that it makes
sense as a time. For example, '101112' is
understood as '10:11:12', but
'109712' is illegal (it has a nonsensical
minute part) and becomes '00:00:00'.

As a number in HHMMSS format, provided
that it makes sense as a time. For example,
101112 is understood as
'10:11:12'. The following alternative
formats are also understood: SS,
MMSS, or HHMMSS.

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
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'.

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:

In numeric contexts, MySQL treats a hexadecimal literal like a
BIGINT (64-bit 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:

For hexadecimal literals, bit operations are considered numeric
context, but bit operations permit numeric or binary string
arguments in MySQL 8.0 and higher. To explicitly
specify binary string context for hexadecimal literals, use a
_binary introducer for at least one of the
arguments:

The displayed result appears similar for both bit operations,
but the result without _binary is a
BIGINT value, whereas the result with
_binary is a binary string. Due to the
difference in result types, the displayed values differ:
High-order 0 digits are not displayed for the numeric result.

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.

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:

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.

For bit literals, bit operations are considered numeric context,
but bit operations permit numeric or binary string arguments in
MySQL 8.0 and higher. To explicitly specify binary
string context for bit literals, use a
_binary introducer for at least one of the
arguments:

The displayed result appears similar for both bit operations,
but the result without _binary is a
BIGINT value, whereas the result with
_binary is a binary string. Due to the
difference in result types, the displayed values differ:
High-order 0 digits are not displayed for the numeric result.

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.

9.2 Schema Object Names

Certain objects within MySQL, including database, table, index,
column, alias, view, stored procedure, partition, tablespace, and
other object names are known as identifiers. This section
describes the permissible syntax for identifiers in MySQL.
Section 9.2.2, “Identifier Case Sensitivity”, describes which
types of identifiers are case sensitive and under what conditions.

An identifier may be quoted or unquoted. If an identifier contains
special characters or is a reserved word, you
must quote it whenever you refer to it.
(Exception: A reserved word that follows a period in a qualified
name must be an identifier, so it need not be quoted.) Reserved
words are listed at Section 9.3, “Keywords and Reserved Words”.

Identifiers are converted to Unicode internally. They may contain
these characters:

The ANSI_QUOTES mode causes the
server to interpret double-quoted strings as identifiers.
Consequently, when this mode is enabled, string literals must be
enclosed within single quotation marks. They cannot be enclosed
within double quotation marks. The server SQL mode is controlled
as described in Section 5.1.8, “Server SQL Modes”.

Identifier quote characters can be included within an identifier
if you quote the identifier. If the character to be included
within the identifier is the same as that used to quote the
identifier itself, then you need to double the character. The
following statement creates a table named a`b
that contains a column named c"d:

mysql> CREATE TABLE `a``b` (`c"d` INT);

In the select list of a query, a quoted column alias can be
specified using identifier or string quoting characters:

Elsewhere in the statement, quoted references to the alias must
use identifier quoting or the reference is treated as a string
literal.

It is recommended that you do not use names that begin with
Me or
MeN,
where M and
N are integers. For example, avoid
using 1e as an identifier, because an
expression such as 1e+3 is ambiguous. Depending
on context, it might be interpreted as the expression 1e
+ 3 or as the number 1e+3.

Be careful when using MD5() to
produce table names because it can produce names in illegal or
ambiguous formats such as those just described.

A user variable cannot be used directly in an SQL statement as an
identifier or as part of an identifier. See
Section 9.4, “User-Defined Variables”, for more information and
examples of workarounds.

The following table describes the maximum length for each type of
identifier.

Identifier

Maximum Length (characters)

Database

64

Table

64

Column

64

Index

64

Constraint

64

Stored Program

64

View

64

Tablespace

64

Server

64

Log File Group

64

Alias

256 (see exception following table)

Compound Statement Label

16

User-Defined Variable

64

Aliases for column names in CREATE
VIEW statements are checked against the maximum column
length of 64 characters (not the maximum alias length of 256
characters).

Identifiers are stored using Unicode (UTF-8). This applies to
identifiers in table definitions and to identifiers stored in the
grant tables in the mysql database. The sizes
of the identifier string columns in the grant tables are measured
in characters. You can use multibyte characters without reducing
the number of characters permitted for values stored in these
columns. As indicated earlier, the permissible Unicode characters
are those in the Basic Multilingual Plane (BMP). Supplementary
characters are not permitted.

9.2.1 Identifier Qualifiers

Object names may be unqualified or qualified. An unqualified
name is permitted in contexts where interpretation of the name
is unambiguous. A qualified name includes at least one qualifier
to clarify the interpretive context by overriding a default
context or providing missing context.

For example, this statement creates a table using the
unqualified name t1:

CREATE TABLE t1 (i INT);

Because t1 includes no qualifier to specify a
database, the statement creates the table in the default
database. If there is no default database, an error occurs.

This statement creates a table using the qualified name
db1.t1:

CREATE TABLE db1.t1 (i INT);

Because db1.t1 includes a database qualifier
db1, the statement creates
t1 in the database named
db1, regardless of the default database. The
qualifier must be specified if there is no
default database. The qualifier may be
specified if there is a default database, to specify a database
different from the default, or to make the database explicit if
the default is the same as the one specified.

Qualifiers have these characteristics:

An unqualified name consists of a single identifier. A
qualified name consists of 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 to interpret the final identifier.

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.

The permitted qualifiers for object names depend on the object
type:

A database name is fully qualified and takes no qualifier:

CREATE DATABASE db1;

A table, view, or stored program name may be given a
database-name qualifier. Examples of unqualified and
qualified names in CREATE statements:

You need not specify a qualifier for an object reference in a
statement unless the unqualified reference is ambiguous. Suppose
that column c1 occurs only in table
t1, c2 only in
t2, and c in both
t1 and t2. Any unqualified
reference to c is ambiguous in a statement
that refers to both tables and must be qualified as
t1.c or t2.c to indicate
which table you mean:

SELECT c1, c2, t1.c FROM t1 INNER JOIN t2
WHERE t2.c > 100;

Similarly, to retrieve from a table t in
database db1 and from a table
t in database db2 in the
same statement, you must qualify the table references: For
references to columns in those tables, qualifiers are required
only for column names that appear in both tables. Suppose that
column c1 occurs only in table
db1.t, c2 only in
db2.t, and c in both
db1.t and db2.t. In this
case, c is ambiguous and must be qualified
but c1 and c2 need not be:

9.2.2 Identifier Case Sensitivity

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.8.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.

Note

Although database, table, and trigger names are not case
sensitive on some platforms, you should not refer to one of
these using different cases within the same statement. The
following statement would not work because it refers to a
table both as my_table and as
MY_TABLE:

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.

Value

Meaning

0

Table and database names are stored on disk using the lettercase
specified in the CREATE
TABLE or CREATE
DATABASE statement. Name comparisons are case
sensitive. You should not set this
variable to 0 if you are running MySQL on a system that
has case-insensitive file names (such as Windows or OS
X). If you force this variable to 0 with
--lower-case-table-names=0
on a case-insensitive file system and access
MyISAM tablenames using different
lettercases, index corruption may result.

1

Table names are stored in lowercase on disk and name comparisons are not
case sensitive. MySQL converts all table names to
lowercase on storage and lookup. This behavior also
applies to database names and table aliases.

2

Table and database names are stored on disk using the lettercase
specified in the CREATE
TABLE or CREATE
DATABASE statement, but MySQL converts them to
lowercase on lookup. Name comparisons are not case
sensitive. This works only on file
systems that are not case sensitive!
InnoDB table names are stored in
lowercase, as for
lower_case_table_names=1.

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:

Use lower_case_table_names=1 on all
systems. The main disadvantage with this is that when you
use SHOW TABLES or
SHOW DATABASES, you do not
see the names in their original lettercase.

Use lower_case_table_names=0 on Unix and
lower_case_table_names=2 on Windows. This
preserves the lettercase of database and table names. The
disadvantage of this is that you must ensure that your
statements always refer to your database and table names
with the correct lettercase on Windows. If you transfer your
statements to Unix, where lettercase is significant, they do
not work if the lettercase is incorrect.

Exception: If you are using
InnoDB tables and you are trying to avoid
these data transfer problems, you should set
lower_case_table_names to 1
on all platforms to force names to be converted to
lowercase.

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:

Reload the dump file for each database. Because
lower_case_table_names is
set, each database and table name will be converted to
lowercase as it is recreated:

mysql < db1.sql
mysql < db2.sql
...

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.

9.2.3 Mapping of Identifiers to File Names

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 depending upon the storage engine, each table may be
represented by one or more files in the appropriate database
directory.

For the data and index files, the exact representation on disk
is storage engine specific. These files may be stored in the
database directory, 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:

All other national letters from alphabets that have
uppercase/lowercase mapping are encoded as shown in the
following table. Values in the Code Range column are UCS-2
values.

Code Range

Pattern

Number

Used

Unused

Blocks

00C0..017F

[@][0..4][g..z]

5*20= 100

97

3

Latin-1 Supplement + Latin Extended-A

0370..03FF

[@][5..9][g..z]

5*20= 100

88

12

Greek and Coptic

0400..052F

[@][g..z][0..6]

20*7= 140

137

3

Cyrillic + Cyrillic Supplement

0530..058F

[@][g..z][7..8]

20*2= 40

38

2

Armenian

2160..217F

[@][g..z][9]

20*1= 20

16

4

Number Forms

0180..02AF

[@][g..z][a..k]

20*11=220

203

17

Latin Extended-B + IPA Extensions

1E00..1EFF

[@][g..z][l..r]

20*7= 140

136

4

Latin Extended Additional

1F00..1FFF

[@][g..z][s..z]

20*8= 160

144

16

Greek Extended

.... ....

[@][a..f][g..z]

6*20= 120

0

120

RESERVED

24B6..24E9

[@][@][a..z]

26

26

0

Enclosed Alphanumerics

FF21..FF5A

[@][a..z][@]

26

26

0

Halfwidth and Fullwidth forms

One of the bytes in the sequence encodes lettercase. For
example: LATIN CAPITAL LETTER A WITH
GRAVE is encoded as @0G,
whereas LATIN SMALL LETTER A WITH GRAVE
is encoded as @0g. Here the third byte
(G or g) indicates
lettercase. (On a case-insensitive file system, both letters
will be treated as the same.)

For some blocks, such as Cyrillic, the second byte
determines lettercase. For other blocks, such as Latin1
Supplement, the third byte determines lettercase. If two
bytes in the sequence are letters (as in Greek Extended),
the leftmost letter character stands for lettercase. All
other letter bytes must be in lowercase.

All nonletter characters except underscore
(_), as well as letters from alphabets
that do not have uppercase/lowercase mapping (such as
Hebrew) are encoded using hexadecimal representation using
lowercase letters for hexadecimal digits
a..f:

0x003F -> @003f
0xFFFF -> @ffff

The hexadecimal values correspond to character values in the
ucs2 double-byte character set.

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.

9.2.4 Function Name Parsing and Resolution

MySQL 8.0 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:

To use the name as a function call in an expression, there
must be no whitespace between the name and the following
( parenthesis character.

Conversely, to use the function name as an identifier, it
must not be followed immediately by a parenthesis.

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/lex.h source file lists the names of
these special functions for which following whitespace
determines their interpretation: names defined by the
SYM_FN() macro in the
symbols[] array.

In MySQL 8.0, there are about 30 such 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.

ADDDATE

BIT_AND

BIT_OR

BIT_XOR

CAST

COUNT

CURDATE

CURTIME

DATE_ADD

DATE_SUB

EXTRACT

GROUP_CONCAT

MAX

MID

MIN

NOW

POSITION

SESSION_USER

STD

STDDEV

STDDEV_POP

STDDEV_SAMP

SUBDATE

SUBSTR

SUBSTRING

SUM

SYSDATE

SYSTEM_USER

TRIM

VARIANCE

VAR_POP

VAR_SAMP

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:

With IGNORE_SPACE
disabled, the parser interprets the name as a function call
when there is no whitespace between the name and the
following parenthesis. This occurs even when the function
name is used in nonexpression context:

With IGNORE_SPACE enabled,
the parser loosens the requirement that there be no
whitespace between the function name and the following
parenthesis. This provides more flexibility in writing
function calls. For example, either of the following
function calls are legal:

SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;

However, enabling
IGNORE_SPACE also has the
side effect that the parser treats the affected function
names as reserved words (see Section 9.3, “Keywords and Reserved Words”).
This means that a space following the name no longer
signifies its use as an identifier. The name can be used in
function calls with or without following whitespace, but
causes a syntax error in nonexpression context unless it is
quoted. For example, with
IGNORE_SPACE enabled, both
of the following statements fail with a syntax error because
the parser interprets count as a reserved
word:

CREATE TABLE count(i INT);
CREATE TABLE count (i INT);

To use the function name in nonexpression context, write it
as a quoted identifier:

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

Avoid creating UDFs or stored functions that have the same
name as a built-in function.

Avoid using function names in nonexpression context. For
example, these statements use count (one
of the affected function names affected by
IGNORE_SPACE), so they
fail with or without whitespace following the name if
IGNORE_SPACE is enabled:

CREATE TABLE count(i INT);
CREATE TABLE count (i INT);

If you must use a function name in nonexpression context,
write it as a quoted identifier:

CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);

Function Name Resolution

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

Built-in functions and user-defined functions

An error occurs if you try to create a UDF with the same
name as a built-in function.

Built-in functions and stored functions

It is possible to create a stored function with the same
name as a built-in function, but to invoke the stored
function it is necessary to qualify it with a schema name.
For example, if you create a stored function named
PI in the test schema,
invoke it as test.PI() because the server
resolves PI() without a
qualifier as a reference to the built-in function. The
server generates a warning if the stored function name
collides with a built-in function name. The warning can be
displayed with SHOW WARNINGS.

User-defined functions and stored functions

User-defined functions and stored functions share the same
namespace, so you cannot create a UDF and a stored function
with the same name.

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

If you have already created a user-defined function with a
given name and upgrade MySQL to a version that implements a
new built-in function with the same name, the UDF becomes
inaccessible. To correct this, use DROP
FUNCTION to drop the UDF and
CREATE FUNCTION to re-create
the UDF with a different nonconflicting name. Then modify
any affected code to use the new name.

If a new version of MySQL implements a built-in function
with the same name as an existing stored function, you have
two choices: Rename the stored function to use a
nonconflicting name, or change calls to the function so that
they use a schema qualifier (that is, use
schema_name.func_name()
syntax). In either case, modify any affected code
accordingly.

9.3 Keywords and Reserved Words

Keywords are words that have significance in SQL. Certain
keywords, such as SELECT,
DELETE, or
BIGINT, are reserved and require
special treatment for use as identifiers such as table and column
names. This may also be true for the names of built-in functions.

Nonreserved keywords are permitted as identifiers without quoting.
Reserved words are permitted as identifiers if you quote them as
described in Section 9.2, “Schema Object Names”:

Names of built-in functions are permitted as identifiers but may
require care to be used as such. For example,
COUNT is acceptable as a column name. However,
by default, no whitespace is permitted in function invocations
between the function name and the following (
character. This requirement enables the parser to distinguish
whether the name is used in a function call or in nonfunction
context. For further details on recognition of function names, see
Section 9.2.4, “Function Name Parsing and Resolution”.

The following table shows the keywords and reserved words in MySQL
8.0, along with changes to individual words from
version to version. Reserved keywords are marked with (R). In
addition, _FILENAME is reserved.

At some point, you might upgrade to a higher version, so it is a
good idea to have a look at future reserved words, too. You can
find these in the manuals that cover higher versions of MySQL.
Most of the reserved words in the table are forbidden by standard
SQL as column or table names (for example,
GROUP). A few are reserved because MySQL needs
them and uses a yacc parser.

9.4 User-Defined Variables

You can store a value in a user-defined variable in one statement
and refer to it later in another statement. This enables you to
pass values from one statement to another.

User variables are written as
@var_name, where the
variable name var_name consists of
alphanumeric characters, .,
_, and $. A user variable
name can contain other characters if you quote it as a string or
identifier (for example, @'my-var',
@"my-var", or @`my-var`).

User-defined variables are session specific. A user variable
defined by one client cannot be seen or used by other clients.
(Exception: A user with access to the Performance Schema
user_variables_by_thread table can
see all user variables for all sessions.) All variables for a
given client session are automatically freed when that client
exits.

User variable names are not case sensitive. Names have a maximum
length of 64 characters.

You can also assign a value to a user variable in statements other
than SET.
In this case, the assignment operator must be
:= and not
= because
the latter is treated as the comparison operator
= in
non-SET
statements:

User variables can be assigned a value from a limited set of data
types: integer, decimal, floating-point, binary or nonbinary
string, or NULL value. Assignment of decimal
and real values does not preserve the precision or scale of the
value. A value of a type other than one of the permissible types
is converted to a permissible type. For example, a value having a
temporal or spatial data type is converted to a binary string. A
value having the JSON data type is
converted to a string with a character set of
utf8mb4 and a collation of
utf8mb4_bin.

If a user variable is assigned a nonbinary (character) string
value, it has the same character set and collation as the string.
The coercibility of user variables is implicit. (This is the same
coercibility as for table column values.)

Hexadcimal or bit values assigned to user variables are treated as
binary strings. To assign a hexadecimal or bit value as a number
to a user variable, use it in numeric context. For example, add 0
or use CAST(... AS UNSIGNED):

If the value of a user variable is selected in a result set, it is
returned to the client as a string.

If you refer to a variable that has not been initialized, it has a
value of NULL and a type of string.

User variables may be used in most contexts where expressions are
permitted. This does not currently include contexts that
explicitly require a literal value, such as in the
LIMIT clause of a
SELECT statement, or the
IGNORE N LINES
clause of a LOAD DATA statement.

As a general rule, other than in
SET
statements, you should never assign a value to a user variable and
read the value within the same statement. For example, to
increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as
SELECT, you might get the results
you expect, but this is not guaranteed. In the following
statement, you might think that MySQL will evaluate
@a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user
variables is undefined.

Another issue with assigning a value to a variable and reading the
value within the same
non-SET
statement is that the default result type of a variable is based
on its type at the start of the statement. The following example
illustrates this:

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL
reports to the client that column one is a string and converts all
accesses of @a to strings, even though @a is
set to a number for the second row. After the
SELECT statement executes,
@a is regarded as a number for the next
statement.

To avoid problems with this behavior, either do not assign a value
to and read the value of the same variable within a single
statement, or else set the variable to 0,
0.0, or '' to define its
type before you use it.

In a SELECT statement, each select
expression is evaluated only when sent to the client. This means
that in a HAVING, GROUP BY,
or ORDER BY clause, referring to a variable
that is assigned a value in the select expression list does
not work as expected:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the
HAVING clause refers to an alias for an
expression in the select list that uses @aa.
This does not work as expected: @aa contains
the value of id from the previous selected row,
not from the current row.

User variables are intended to provide data values. They cannot be
used directly in an SQL statement as an identifier or as part of
an identifier, such as in contexts where a table or database name
is expected, or as a reserved word such as
SELECT. This is true even if the
variable is quoted, as shown in the following example:

An exception to this principle that user variables cannot be used
to provide identifiers, is when you are constructing a string for
use as a prepared statement to execute later. In this case, user
variables can be used to provide any part of the statement. The
following example illustrates how this can be done:

Assembling an SQL statement in this fashion is sometimes known as
“Dynamic SQL”.

9.5 Expression Syntax

The following rules define expression syntax in MySQL. The grammar
shown here is based on that given in the
sql/sql_yacc.yy file of MySQL source
distributions. See the notes after the grammar for additional
information about some of the terms.

{identifierexpr} is ODBC escape syntax
and is accepted for ODBC compatibility. The value is
expr. The curly braces in the syntax
should be written literally; they are not metasyntax as used
elsewhere in syntax descriptions.

interval_expr represents a time
interval. The syntax is INTERVAL
exprunit, where
unit is a specifier such as
HOUR, DAY, or
WEEK. For the full list of
unit specifiers, see the description of
the DATE_ADD() function in
Section 12.7, “Date and Time Functions”.

The meaning of some operators depends on the SQL mode:

By default, || is
a logical OR operator. With
PIPES_AS_CONCAT enabled,
|| is string
concatenation, with a precedence between
^ and the
unary operators.

By default, ! has
a higher precedence than NOT. With
HIGH_NOT_PRECEDENCE enabled,
! and
NOT have the same precedence.

9.6 Comment Syntax

MySQL Server supports three comment styles:

From a # character to the end of the line.

From a -- sequence to the end of the
line. In MySQL, the -- (double-dash)
comment style requires the second dash to be followed by at
least one whitespace or control character (such as a space,
tab, newline, and so on). This syntax differs slightly from
standard SQL comment syntax, as discussed in
Section 1.8.2.4, “'--' as the Start of a Comment”.

From a /* sequence to the following
*/ sequence, as in the C programming
language. This syntax enables a comment to extend over
multiple lines because the beginning and closing sequences
need not be on the same line.

The following example demonstrates all three comment styles:

mysql> SELECT 1+1; # This comment continues to the end of line
mysql> SELECT 1+1; -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+/*this is amultiple-line comment*/1;

Nested comments are not supported. (Under some conditions, nested
comments might be permitted, but usually are not, and users should
avoid them.)

MySQL Server supports some variants of C-style comments. These
enable you to write code that includes MySQL extensions, but is
still portable, by using comments of the following form:

/*! MySQL-specific code */

In this case, MySQL Server parses and executes the code within the
comment as it would any other SQL statement, but other SQL servers
will ignore the extensions. For example, MySQL Server recognizes
the STRAIGHT_JOIN keyword in the following
statement, but other servers will not:

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

If you add a version number after the !
character, the syntax within the comment is executed only if the
MySQL version is greater than or equal to the specified version
number. The KEY_BLOCK_SIZE keyword in the
following comment is executed only by servers from MySQL 5.1.10 or
higher:

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

The comment syntax just described applies to how the
mysqld server parses SQL statements. The
mysql client program also performs some parsing
of statements before sending them to the server. (It does this to
determine statement boundaries within a multiple-statement input
line.)

Comments in this format, /*!12345 ... */, are
not stored on the server. If this format is used to comment stored
routines, the comments will not be retained on the server.

Another variant of C-style comment syntax is used to specify
optimizer hints. Hint comments include a +
character following the /* comment opening
sequence. Example: