Table of Contents
This chapter discusses the rules for writing the following elements of SQL statements when using MySQL:
Literal values such as strings and numbers
Identifiers such as database, table, and column names
Keywords and reserved words
User-defined and system variables
Comments
This section describes how to write literal values in MySQL. These
include strings, numbers, hexadecimal values, boolean values, and
NULL
. The section also covers the various
nuances and “gotchas” that you may run into when
dealing with these basic types in MySQL.
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
' [COLLATEcollation_name
]
Examples:
SELECT _latin1'string
'; SELECT _latin1'string
' COLLATE latin1_danish_ci;
You can use
N'
(or
literal
'n'
) to
create a string in the national character set. These statements
are equivalent:
literal
'
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.3.5, “Character String Literal Character Set and Collation”, and Section 10.1.3.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.3.5, “Character String Literal Character Set and Collation”.
Table 9.1 Special Character Escape Sequences
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:
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:
Process the string with a function that escapes the special
characters. In a C program, you can use the
mysql_real_escape_string()
C
API function to escape characters. See
Section 24.8.7.55, “mysql_real_escape_string()”. Within SQL
statements that construct other SQL statements, you can use
the QUOTE()
function. The
Perl DBI interface provides a quote
method to convert special characters to the proper escape
sequences. See Section 24.10, “MySQL Perl API”. Other language
interfaces may provide a similar capability.
As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that enables you to insert special markers into a statement string, and then bind data values to them when you issue the statement. In this case, the API takes care of escaping special characters in the values for you.
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.21, “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
' }
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'
.
MySQL recognizes DATETIME
and
TIMESTAMP
values in these
formats:
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:
Year values in the range 70-99
are
converted to 1970-1999
.
Year values in the range 00-69
are
converted to 2000-2069
.
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:
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'
.
MySQL supports hexadecimal values, written using
X'
,
val
'x'
, or
val
'0x
format,
where val
val
contains hexadecimal digits
(0..9
, A..F
). Lettercase
of the digits does not matter. For values written using
X'
or
val
'x'
format,
val
'val
must contain an even number of
digits. For values written using
0x
,
values that contain an odd number of digits are treated as
having an extra leading val
syntax0
. 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'
and hexstring
'x'
syntaxes are based on standard SQL. The val
'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'
or
value
'0b
notation.
value
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”.
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:
Permitted characters in unquoted identifiers:
ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
Extended: U+0080 .. U+FFFF
Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:
ASCII: U+0001 .. U+007F
Extended: U+0080 .. U+FFFF
ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.
Identifiers may begin with a digit but unless quoted may not consist solely of digits.
Database, table, and column names cannot end with space characters.
The identifier quote character is the backtick
(“`
”):
mysql> SELECT * FROM `select` WHERE `select`.id > 100;
If the ANSI_QUOTES
SQL mode is
enabled, it is also permissible to quote identifiers within double
quotation marks:
mysql>CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax... mysql>SET sql_mode='ANSI_QUOTES';
mysql>CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)
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.7, “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:
mysql> SELECT 1 AS `one`, 2 AS 'two';
+-----+-----+
| one | two |
+-----+-----+
| 1 | 2 |
+-----+-----+
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
or
M
e
,
where M
eN
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.
Special characters in database and table names are encoded in the
corresponding file system names as described in
Section 9.2.3, “Mapping of Identifiers to File Names”. If you have databases or
tables from an older version of MySQL 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#
. For information about referring to
such names or converting them to the newer encoding, see that
section.
The following table describes the maximum length for each type of identifier.
Identifier | Maximum Length (characters) |
---|---|
Database | 64 (NDB storage engine: 63) |
Table | 64 (NDB storage engine: 63) |
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 as of MySQL 5.7.5, no limit before that |
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 that are stored in
.frm
files 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.
MySQL Cluster imposes a maximum length of 63 characters for names of databases and tables. See Section 18.1.6.5, “Limits Associated with Database Objects in MySQL Cluster”.
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.
Column Reference | Meaning |
---|---|
col_name | The column col_name from whichever table used
in the statement contains a column of that name. |
tbl_name.col_name | The column col_name from table
tbl_name of the default
database. |
db_name.tbl_name.col_name | The column col_name from table
tbl_name of the database
db_name . |
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.
and
col_name
db2.t.
.
col_name
The syntax
means
the table .tbl_name
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.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.
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
FROMtbl_name
AS aWHERE 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
:
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
:
Use mysqldump to dump each database:
mysqldump --databases db1 > db1.sql mysqldump --databases db2 > db2.sql ...
Do this for each database that must be recreated.
Use DROP DATABASE
to drop each database.
Stop the server, set
lower_case_table_names
, and
restart the server.
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.
File system case sensitivity can affect searches in string
columns of INFORMATION_SCHEMA
tables. For
more information, see
Section 10.1.7.9, “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:
Basic Latin letters (a..zA..Z
), digits
(0..9
) and underscore
(_
) are encoded as is. Consequently,
their case sensitivity directly depends on file system
features.
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 hex 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.
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”.
The #mysql50#
prefix is intended only to be
used internally by the server. You should not create databases
or tables with names that use this prefix.
Also, mysqlcheck cannot fix names that
contain literal instances of the @
character that is used for encoding special characters. If you
have databases or tables that contain this character, use
mysqldump to dump them before upgrading to
MySQL 5.1.6 or later, and then reload the dump file after
upgrading.
Conversion of pre-MySQL 5.1 database names containing special
characters to 5.1 format with the addition of a
#mysql50#
prefix is deprecated as of MySQL
5.7.6 and will be removed in a future version of MySQL.
Because such conversions are deprecated, the
--fix-db-names
and
--fix-table-names
options
for mysqlcheck and the UPGRADE
DATA DIRECTORY NAME
clause for the
ALTER DATABASE
statement are
also deprecated.
Upgrades are supported only from one release series to another (for example, 5.0 to 5.1, or 5.1 to 5.5), so there should be little remaining need for conversion of older 5.0 database names to current versions of MySQL. As a workaround, upgrade a MySQL 5.0 installation to MySQL 5.1 before upgrading to a more recent release.
MySQL 5.7 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:
MySQL 5.7.7 and up: Names defined by the
SYM_FN()
macro in the
symbols[]
array
Before MySQL 5.7.7: Names listed in the
sql_functions[]
array
In MySQL 5.7, 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:
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.
func_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:
mysql> CREATE TABLE count(i INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'count(i INT)'
To eliminate the error and cause the name to be treated as an identifier, either use whitespace following the name or write it as a quoted identifier (or both):
CREATE TABLE count (i INT); CREATE TABLE `count`(i INT); CREATE TABLE `count` (i INT);
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:
CREATE TABLE `count`(i INT); CREATE TABLE `count` (i INT);
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:
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
syntax). In either case, modify any affected code
accordingly.
schema_name
.func_name
()
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”:
mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'
BEGIN
and END
are keywords
but not reserved, so their use as identifiers does not require
quoting. INTERVAL
is a reserved keyword and
must be quoted to be used as an identifier:
mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)
Exception: A word that follows a period in a qualified name must be an identifier, so it need not be quoted even if it is reserved:
mysql> CREATE TABLE mydb.interval (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)
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
5.7, 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.
Table 9.2 Keywords and Reserved Words in MySQL 5.7
ACCESSIBLE (R) |
ACCOUNT [a] |
ACTION |
|||||||||||||||||||||||||||||||||||||||||||||||
ADD (R) |
AFTER |
AGAINST |
|||||||||||||||||||||||||||||||||||||||||||||||
AGGREGATE |
ALGORITHM |
ALL (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
ALTER (R) |
ALWAYS [b] |
ANALYSE |
|||||||||||||||||||||||||||||||||||||||||||||||
ANALYZE (R) |
AND (R) |
ANY |
|||||||||||||||||||||||||||||||||||||||||||||||
AS (R) |
ASC (R) |
ASCII |
|||||||||||||||||||||||||||||||||||||||||||||||
ASENSITIVE (R) |
AT |
AUTOEXTEND_SIZE |
|||||||||||||||||||||||||||||||||||||||||||||||
AUTO_INCREMENT |
AVG |
AVG_ROW_LENGTH |
|||||||||||||||||||||||||||||||||||||||||||||||
BACKUP |
BEFORE (R) |
BEGIN |
|||||||||||||||||||||||||||||||||||||||||||||||
BETWEEN (R) |
BIGINT (R) |
BINARY (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
BINLOG |
BIT |
BLOB (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
BLOCK |
BOOL |
BOOLEAN |
|||||||||||||||||||||||||||||||||||||||||||||||
BOTH (R) |
BTREE |
BY (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
BYTE |
CACHE |
CALL (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
CASCADE (R) |
CASCADED |
CASE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
CATALOG_NAME |
CHAIN |
CHANGE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
CHANGED |
CHANNEL [c] |
CHAR (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
CHARACTER (R) |
CHARSET |
CHECK (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
CHECKSUM |
CIPHER |
CLASS_ORIGIN |
|||||||||||||||||||||||||||||||||||||||||||||||
CLIENT |
CLOSE |
COALESCE |
|||||||||||||||||||||||||||||||||||||||||||||||
CODE |
COLLATE (R) |
COLLATION |
|||||||||||||||||||||||||||||||||||||||||||||||
COLUMN (R) |
COLUMNS |
COLUMN_FORMAT |
|||||||||||||||||||||||||||||||||||||||||||||||
COLUMN_NAME |
COMMENT |
COMMIT |
|||||||||||||||||||||||||||||||||||||||||||||||
COMMITTED |
COMPACT |
COMPLETION |
|||||||||||||||||||||||||||||||||||||||||||||||
COMPRESSED |
COMPRESSION [d] |
CONCURRENT |
|||||||||||||||||||||||||||||||||||||||||||||||
CONDITION (R) |
CONNECTION |
CONSISTENT |
|||||||||||||||||||||||||||||||||||||||||||||||
CONSTRAINT (R) |
CONSTRAINT_CATALOG |
CONSTRAINT_NAME |
|||||||||||||||||||||||||||||||||||||||||||||||
CONSTRAINT_SCHEMA |
CONTAINS |
CONTEXT |
|||||||||||||||||||||||||||||||||||||||||||||||
CONTINUE (R) |
CONVERT (R) |
CPU |
|||||||||||||||||||||||||||||||||||||||||||||||
CREATE (R) |
CROSS (R) |
CUBE |
|||||||||||||||||||||||||||||||||||||||||||||||
CURRENT |
CURRENT_DATE (R) |
CURRENT_TIME (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
CURRENT_TIMESTAMP (R) |
CURRENT_USER (R) |
CURSOR (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
CURSOR_NAME |
DATA |
DATABASE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
DATABASES (R) |
DATAFILE |
DATE |
|||||||||||||||||||||||||||||||||||||||||||||||
DATETIME |
DAY |
DAY_HOUR (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
DAY_MICROSECOND (R) |
DAY_MINUTE (R) |
DAY_SECOND (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
DEALLOCATE |
DEC (R) |
DECIMAL (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
DECLARE (R) |
DEFAULT (R) |
DEFAULT_AUTH |
|||||||||||||||||||||||||||||||||||||||||||||||
DEFINER |
DELAYED (R) |
DELAY_KEY_WRITE |
|||||||||||||||||||||||||||||||||||||||||||||||
DELETE (R) |
DESC (R) |
DESCRIBE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
DES_KEY_FILE |
DETERMINISTIC (R) |
DIAGNOSTICS |
|||||||||||||||||||||||||||||||||||||||||||||||
DIRECTORY |
DISABLE |
DISCARD |
|||||||||||||||||||||||||||||||||||||||||||||||
DISK |
DISTINCT (R) |
DISTINCTROW (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
DIV (R) |
DO |
DOUBLE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
DROP (R) |
DUAL (R) |
DUMPFILE |
|||||||||||||||||||||||||||||||||||||||||||||||
DUPLICATE |
DYNAMIC |
EACH (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
ELSE (R) |
ELSEIF (R) |
ENABLE |
|||||||||||||||||||||||||||||||||||||||||||||||
ENCLOSED (R) |
ENCRYPTION [e] |
END |
|||||||||||||||||||||||||||||||||||||||||||||||
ENDS |
ENGINE |
ENGINES |
|||||||||||||||||||||||||||||||||||||||||||||||
ENUM |
ERROR |
ERRORS |
|||||||||||||||||||||||||||||||||||||||||||||||
ESCAPE |
ESCAPED (R) |
EVENT |
|||||||||||||||||||||||||||||||||||||||||||||||
EVENTS |
EVERY |
EXCHANGE |
|||||||||||||||||||||||||||||||||||||||||||||||
EXECUTE |
EXISTS (R) |
EXIT (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
EXPANSION |
EXPIRE |
EXPLAIN (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
EXPORT |
EXTENDED |
EXTENT_SIZE |
|||||||||||||||||||||||||||||||||||||||||||||||
FALSE (R) |
FAST |
FAULTS |
|||||||||||||||||||||||||||||||||||||||||||||||
FETCH (R) |
FIELDS |
FILE |
|||||||||||||||||||||||||||||||||||||||||||||||
FILE_BLOCK_SIZE [f] |
FILTER [g] |
FIRST |
|||||||||||||||||||||||||||||||||||||||||||||||
FIXED |
FLOAT (R) |
FLOAT4 (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
FLOAT8 (R) |
FLUSH |
FOLLOWS [h] |
|||||||||||||||||||||||||||||||||||||||||||||||
FOR (R) |
FORCE (R) |
FOREIGN (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
FORMAT |
FOUND |
FROM (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
FULL |
FULLTEXT (R) |
FUNCTION |
|||||||||||||||||||||||||||||||||||||||||||||||
GENERAL |
GENERATED [i] (R) |
GEOMETRY |
|||||||||||||||||||||||||||||||||||||||||||||||
GEOMETRYCOLLECTION |
GET (R) |
GET_FORMAT |
|||||||||||||||||||||||||||||||||||||||||||||||
GLOBAL |
GRANT (R) |
GRANTS |
|||||||||||||||||||||||||||||||||||||||||||||||
GROUP (R) |
GROUP_REPLICATION [j] |
HANDLER |
|||||||||||||||||||||||||||||||||||||||||||||||
HASH |
HAVING (R) |
HELP |
|||||||||||||||||||||||||||||||||||||||||||||||
HIGH_PRIORITY (R) |
HOST |
HOSTS |
|||||||||||||||||||||||||||||||||||||||||||||||
HOUR |
HOUR_MICROSECOND (R) |
HOUR_MINUTE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
HOUR_SECOND (R) |
IDENTIFIED |
IF (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
IGNORE (R) |
IGNORE_SERVER_IDS |
IMPORT |
|||||||||||||||||||||||||||||||||||||||||||||||
IN (R) |
INDEX (R) |
INDEXES |
|||||||||||||||||||||||||||||||||||||||||||||||
INFILE (R) |
INITIAL_SIZE |
INNER (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
INOUT (R) |
INSENSITIVE (R) |
INSERT (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
INSERT_METHOD |
INSTALL |
INSTANCE [k] |
|||||||||||||||||||||||||||||||||||||||||||||||
INT (R) |
INT1 (R) |
INT2 (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
INT3 (R) |
INT4 (R) |
INT8 (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
INTEGER (R) |
INTERVAL (R) |
INTO (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
INVOKER |
IO |
IO_AFTER_GTIDS (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
IO_BEFORE_GTIDS (R) |
IO_THREAD |
IPC |
|||||||||||||||||||||||||||||||||||||||||||||||
IS (R) |
ISOLATION |
ISSUER |
|||||||||||||||||||||||||||||||||||||||||||||||
ITERATE (R) |
JOIN (R) |
JSON [l] |
|||||||||||||||||||||||||||||||||||||||||||||||
KEY (R) |
KEYS (R) |
KEY_BLOCK_SIZE |
|||||||||||||||||||||||||||||||||||||||||||||||
KILL (R) |
LANGUAGE |
LAST |
|||||||||||||||||||||||||||||||||||||||||||||||
LEADING (R) |
LEAVE (R) |
LEAVES |
|||||||||||||||||||||||||||||||||||||||||||||||
LEFT (R) |
LESS |
LEVEL |
|||||||||||||||||||||||||||||||||||||||||||||||
LIKE (R) |
LIMIT (R) |
LINEAR (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
LINES (R) |
LINESTRING |
LIST |
|||||||||||||||||||||||||||||||||||||||||||||||
LOAD (R) |
LOCAL |
LOCALTIME (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
LOCALTIMESTAMP (R) |
LOCK (R) |
LOCKS |
|||||||||||||||||||||||||||||||||||||||||||||||
LOGFILE |
LOGS |
LONG (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
LONGBLOB (R) |
LONGTEXT (R) |
LOOP (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
LOW_PRIORITY (R) |
MASTER |
MASTER_AUTO_POSITION |
|||||||||||||||||||||||||||||||||||||||||||||||
MASTER_BIND (R) |
MASTER_CONNECT_RETRY |
MASTER_DELAY |
|||||||||||||||||||||||||||||||||||||||||||||||
MASTER_HEARTBEAT_PERIOD |
MASTER_HOST |
MASTER_LOG_FILE |
|||||||||||||||||||||||||||||||||||||||||||||||
MASTER_LOG_POS |
MASTER_PASSWORD |
MASTER_PORT |
|||||||||||||||||||||||||||||||||||||||||||||||
MASTER_RETRY_COUNT |
MASTER_SERVER_ID |
MASTER_SSL |
|||||||||||||||||||||||||||||||||||||||||||||||
MASTER_SSL_CA |
MASTER_SSL_CAPATH |
MASTER_SSL_CERT |
|||||||||||||||||||||||||||||||||||||||||||||||
MASTER_SSL_CIPHER |
MASTER_SSL_CRL |
MASTER_SSL_CRLPATH |
|||||||||||||||||||||||||||||||||||||||||||||||
MASTER_SSL_KEY |
MASTER_SSL_VERIFY_SERVER_CERT (R) |
MASTER_TLS_VERSION [m] |
|||||||||||||||||||||||||||||||||||||||||||||||
MASTER_USER |
MATCH (R) |
MAXVALUE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
MAX_CONNECTIONS_PER_HOUR |
MAX_QUERIES_PER_HOUR |
MAX_ROWS |
|||||||||||||||||||||||||||||||||||||||||||||||
MAX_SIZE |
MAX_STATEMENT_TIME [n] |
MAX_UPDATES_PER_HOUR |
|||||||||||||||||||||||||||||||||||||||||||||||
MAX_USER_CONNECTIONS |
MEDIUM |
MEDIUMBLOB (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
MEDIUMINT (R) |
MEDIUMTEXT (R) |
MEMORY |
|||||||||||||||||||||||||||||||||||||||||||||||
MERGE |
MESSAGE_TEXT |
MICROSECOND |
|||||||||||||||||||||||||||||||||||||||||||||||
MIDDLEINT (R) |
MIGRATE |
MINUTE |
|||||||||||||||||||||||||||||||||||||||||||||||
MINUTE_MICROSECOND (R) |
MINUTE_SECOND (R) |
MIN_ROWS |
|||||||||||||||||||||||||||||||||||||||||||||||
MOD (R) |
MODE |
MODIFIES (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
MODIFY |
MONTH |
MULTILINESTRING |
|||||||||||||||||||||||||||||||||||||||||||||||
MULTIPOINT |
MULTIPOLYGON |
MUTEX |
|||||||||||||||||||||||||||||||||||||||||||||||
MYSQL_ERRNO |
NAME |
NAMES |
|||||||||||||||||||||||||||||||||||||||||||||||
NATIONAL |
NATURAL (R) |
NCHAR |
|||||||||||||||||||||||||||||||||||||||||||||||
NDB |
NDBCLUSTER |
NEVER [o] |
|||||||||||||||||||||||||||||||||||||||||||||||
NEW |
NEXT |
NO |
|||||||||||||||||||||||||||||||||||||||||||||||
NODEGROUP |
NONBLOCKING [p] |
NONE |
|||||||||||||||||||||||||||||||||||||||||||||||
NOT (R) |
NO_WAIT |
NO_WRITE_TO_BINLOG (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
NULL (R) |
NUMBER |
NUMERIC (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
NVARCHAR |
OFFSET |
OLD_PASSWORD [q] |
|||||||||||||||||||||||||||||||||||||||||||||||
ON (R) |
ONE |
ONLY |
|||||||||||||||||||||||||||||||||||||||||||||||
OPEN |
OPTIMIZE (R) |
OPTIMIZER_COSTS [r] (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
OPTION (R) |
OPTIONALLY (R) |
OPTIONS |
|||||||||||||||||||||||||||||||||||||||||||||||
OR (R) |
ORDER (R) |
OUT (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
OUTER (R) |
OUTFILE (R) |
OWNER |
|||||||||||||||||||||||||||||||||||||||||||||||
PACK_KEYS |
PAGE |
PARSER |
|||||||||||||||||||||||||||||||||||||||||||||||
PARSE_GCOL_EXPR [s] |
PARTIAL |
PARTITION (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
PARTITIONING |
PARTITIONS |
PASSWORD |
|||||||||||||||||||||||||||||||||||||||||||||||
PHASE |
PLUGIN |
PLUGINS |
|||||||||||||||||||||||||||||||||||||||||||||||
PLUGIN_DIR |
POINT |
POLYGON |
|||||||||||||||||||||||||||||||||||||||||||||||
PORT |
PRECEDES [t] |
PRECISION (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
PREPARE |
PRESERVE |
PREV |
|||||||||||||||||||||||||||||||||||||||||||||||
PRIMARY (R) |
PRIVILEGES |
PROCEDURE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
PROCESSLIST |
PROFILE |
PROFILES |
|||||||||||||||||||||||||||||||||||||||||||||||
PROXY |
PURGE (R) |
QUARTER |
|||||||||||||||||||||||||||||||||||||||||||||||
QUERY |
QUICK |
RANGE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
READ (R) |
READS (R) |
READ_ONLY |
|||||||||||||||||||||||||||||||||||||||||||||||
READ_WRITE (R) |
REAL (R) |
REBUILD |
|||||||||||||||||||||||||||||||||||||||||||||||
RECOVER |
REDOFILE |
REDO_BUFFER_SIZE |
|||||||||||||||||||||||||||||||||||||||||||||||
REDUNDANT |
REFERENCES (R) |
REGEXP (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
RELAY |
RELAYLOG |
RELAY_LOG_FILE |
|||||||||||||||||||||||||||||||||||||||||||||||
RELAY_LOG_POS |
RELAY_THREAD |
RELEASE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
RELOAD |
REMOVE |
RENAME (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
REORGANIZE |
REPAIR |
REPEAT (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
REPEATABLE |
REPLACE (R) |
REPLICATE_DO_DB [u] |
|||||||||||||||||||||||||||||||||||||||||||||||
REPLICATE_DO_TABLE [v] |
REPLICATE_IGNORE_DB [w] |
REPLICATE_IGNORE_TABLE [x] |
|||||||||||||||||||||||||||||||||||||||||||||||
REPLICATE_REWRITE_DB [y] |
REPLICATE_WILD_DO_TABLE [z] |
REPLICATE_WILD_IGNORE_TABLE [aa] |
|||||||||||||||||||||||||||||||||||||||||||||||
REPLICATION |
REQUIRE (R) |
RESET |
|||||||||||||||||||||||||||||||||||||||||||||||
RESIGNAL (R) |
RESTORE |
RESTRICT (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
RESUME |
RETURN (R) |
RETURNED_SQLSTATE |
|||||||||||||||||||||||||||||||||||||||||||||||
RETURNS |
REVERSE |
REVOKE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
RIGHT (R) |
RLIKE (R) |
ROLLBACK |
|||||||||||||||||||||||||||||||||||||||||||||||
ROLLUP |
ROTATE [ab] |
ROUTINE |
|||||||||||||||||||||||||||||||||||||||||||||||
ROW |
ROWS |
ROW_COUNT |
|||||||||||||||||||||||||||||||||||||||||||||||
ROW_FORMAT |
RTREE |
SAVEPOINT |
|||||||||||||||||||||||||||||||||||||||||||||||
SCHEDULE |
SCHEMA (R) |
SCHEMAS (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
SCHEMA_NAME |
SECOND |
SECOND_MICROSECOND (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
SECURITY |
SELECT (R) |
SENSITIVE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
SEPARATOR (R) |
SERIAL |
SERIALIZABLE |
|||||||||||||||||||||||||||||||||||||||||||||||
SERVER |
SESSION |
SET (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
SHARE |
SHOW (R) |
SHUTDOWN |
|||||||||||||||||||||||||||||||||||||||||||||||
SIGNAL (R) |
SIGNED |
SIMPLE |
|||||||||||||||||||||||||||||||||||||||||||||||
SLAVE |
SLOW |
SMALLINT (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
SNAPSHOT |
SOCKET |
SOME |
|||||||||||||||||||||||||||||||||||||||||||||||
SONAME |
SOUNDS |
SOURCE |
|||||||||||||||||||||||||||||||||||||||||||||||
SPATIAL (R) |
SPECIFIC (R) |
SQL (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
SQLEXCEPTION (R) |
SQLSTATE (R) |
SQLWARNING (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
SQL_AFTER_GTIDS |
SQL_AFTER_MTS_GAPS |
SQL_BEFORE_GTIDS |
|||||||||||||||||||||||||||||||||||||||||||||||
SQL_BIG_RESULT (R) |
SQL_BUFFER_RESULT |
SQL_CACHE |
|||||||||||||||||||||||||||||||||||||||||||||||
SQL_CALC_FOUND_ROWS (R) |
SQL_NO_CACHE |
SQL_SMALL_RESULT (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
SQL_THREAD |
SQL_TSI_DAY |
SQL_TSI_HOUR |
|||||||||||||||||||||||||||||||||||||||||||||||
SQL_TSI_MINUTE |
SQL_TSI_MONTH |
SQL_TSI_QUARTER |
|||||||||||||||||||||||||||||||||||||||||||||||
SQL_TSI_SECOND |
SQL_TSI_WEEK |
SQL_TSI_YEAR |
|||||||||||||||||||||||||||||||||||||||||||||||
SSL (R) |
STACKED |
START |
|||||||||||||||||||||||||||||||||||||||||||||||
STARTING (R) |
STARTS |
STATS_AUTO_RECALC |
|||||||||||||||||||||||||||||||||||||||||||||||
STATS_PERSISTENT |
STATS_SAMPLE_PAGES |
STATUS |
|||||||||||||||||||||||||||||||||||||||||||||||
STOP |
STORAGE |
STORED [ac] (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
STRAIGHT_JOIN (R) |
STRING |
SUBCLASS_ORIGIN |
|||||||||||||||||||||||||||||||||||||||||||||||
SUBJECT |
SUBPARTITION |
SUBPARTITIONS |
|||||||||||||||||||||||||||||||||||||||||||||||
SUPER |
SUSPEND |
SWAPS |
|||||||||||||||||||||||||||||||||||||||||||||||
SWITCHES |
TABLE (R) |
TABLES |
|||||||||||||||||||||||||||||||||||||||||||||||
TABLESPACE |
TABLE_CHECKSUM |
TABLE_NAME |
|||||||||||||||||||||||||||||||||||||||||||||||
TEMPORARY |
TEMPTABLE |
TERMINATED (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
TEXT |
THAN |
THEN (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
TIME |
TIMESTAMP |
TIMESTAMPADD |
|||||||||||||||||||||||||||||||||||||||||||||||
TIMESTAMPDIFF |
TINYBLOB (R) |
TINYINT (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
TINYTEXT (R) |
TO (R) |
TRAILING (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
TRANSACTION |
TRIGGER (R) |
TRIGGERS |
|||||||||||||||||||||||||||||||||||||||||||||||
TRUE (R) |
TRUNCATE |
TYPE |
|||||||||||||||||||||||||||||||||||||||||||||||
TYPES |
UNCOMMITTED |
UNDEFINED |
|||||||||||||||||||||||||||||||||||||||||||||||
UNDO (R) |
UNDOFILE |
UNDO_BUFFER_SIZE |
|||||||||||||||||||||||||||||||||||||||||||||||
UNICODE |
UNINSTALL |
UNION (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
UNIQUE (R) |
UNKNOWN |
UNLOCK (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
UNSIGNED (R) |
UNTIL |
UPDATE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
UPGRADE |
USAGE (R) |
USE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
USER |
USER_RESOURCES |
USE_FRM |
|||||||||||||||||||||||||||||||||||||||||||||||
USING (R) |
UTC_DATE (R) |
UTC_TIME (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
UTC_TIMESTAMP (R) |
VALIDATION [ad] |
VALUE |
|||||||||||||||||||||||||||||||||||||||||||||||
VALUES (R) |
VARBINARY (R) |
VARCHAR (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
VARCHARACTER (R) |
VARIABLES |
VARYING (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
VIEW |
VIRTUAL [ae] (R) |
WAIT |
|||||||||||||||||||||||||||||||||||||||||||||||
WARNINGS |
WEEK |
WEIGHT_STRING |
|||||||||||||||||||||||||||||||||||||||||||||||
WHEN (R) |
WHERE (R) |
WHILE (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
WITH (R) |
WITHOUT [af] |
WORK |
|||||||||||||||||||||||||||||||||||||||||||||||
WRAPPER |
WRITE (R) |
X509 |
|||||||||||||||||||||||||||||||||||||||||||||||
XA |
XID [ag] |
XML |
|||||||||||||||||||||||||||||||||||||||||||||||
XOR (R) |
YEAR |
YEAR_MONTH (R) |
|||||||||||||||||||||||||||||||||||||||||||||||
ZEROFILL (R) |
|||||||||||||||||||||||||||||||||||||||||||||||||
[a] [b] [c] [d] [e] [f] [g] [h] [i] [j] [k] [l] [m] [n] [o] [p] [q] [r] [s] [t] [u] [v] [w] [x] [y] [z] [aa] [ab] [ac] [ad] [ae] [af] [ag] |
The following table shows the keywords and reserved words that are new in MySQL 5.7. Reserved keywords are marked with (R).
Table 9.3 New Keywords and Reserved Words in MySQL 5.7 compared to MySQL 5.6
ACCOUNT |
ALWAYS |
CHANNEL |
COMPRESSION |
ENCRYPTION |
FILE_BLOCK_SIZE |
FILTER |
FOLLOWS |
GENERATED (R) |
GROUP_REPLICATION |
INSTANCE |
JSON |
MASTER_TLS_VERSION |
NEVER |
OPTIMIZER_COSTS (R) |
PARSE_GCOL_EXPR |
PRECEDES |
REPLICATE_DO_DB |
REPLICATE_DO_TABLE |
REPLICATE_IGNORE_DB |
REPLICATE_IGNORE_TABLE |
REPLICATE_REWRITE_DB |
REPLICATE_WILD_DO_TABLE |
REPLICATE_WILD_IGNORE_TABLE |
ROTATE |
STACKED |
STORED (R) |
VALIDATION |
VIRTUAL (R) |
WITHOUT |
XID |
You can store a value in a user-defined variable in one statement and then refer to it later in another statement. This enables you to pass values from one statement to another.
User variables are written as
@
, where the
variable name var_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 as of MySQL 5.7.5. (Length is not constrained before that.)
One way to set a user-defined variable is by issuing a
SET
statement:
SET @var_name
=expr
[, @var_name
=expr
] ...
For SET
,
either =
or
:=
can be
used as the assignment operator.
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:
mysql>SET @t1=1, @t2=2, @t3:=4;
mysql>SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+ | @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 | +------+------+------+--------------------+ | 1 | 2 | 4 | 7 | +------+------+------+--------------------+
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.)
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 = b'1000001';
mysql>SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001'+0;
mysql>SELECT @v1, @v2, @v3;
+------+------+------+ | @v1 | @v2 | @v3 | +------+------+------+ | A | 65 | 65 | +------+------+------+
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
clause of a N
LINESLOAD 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:
mysql>SELECT c1 FROM t;
+----+ | c1 | +----+ | 0 | +----+ | 1 | +----+ 2 rows in set (0.00 sec) mysql>SET @col = "c1";
Query OK, 0 rows affected (0.00 sec) mysql>SELECT @col FROM t;
+------+ | @col | +------+ | c1 | +------+ 1 row in set (0.00 sec) mysql>SELECT `@col` FROM t;
ERROR 1054 (42S22): Unknown column '@col' in 'field list' mysql> SET @col = "`c1`"; Query OK, 0 rows affected (0.00 sec) mysql>SELECT @col FROM t;
+------+ | @col | +------+ | `c1` | +------+ 1 row in set (0.00 sec)
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:
mysql>SET @c = "c1";
Query OK, 0 rows affected (0.00 sec) mysql>SET @s = CONCAT("SELECT ", @c, " FROM t");
Query OK, 0 rows affected (0.00 sec) mysql>PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.04 sec) Statement prepared mysql>EXECUTE stmt;
+----+ | c1 | +----+ | 0 | +----+ | 1 | +----+ 2 rows in set (0.00 sec) mysql>DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
See Section 13.5, “SQL Syntax for Prepared Statements”, for more information.
A similar technique can be used in application programs to construct SQL statements using program variables, as shown here using PHP 5:
<?php $mysqli = new mysqli("localhost", "user", "pass", "test"); if( mysqli_connect_errno() ) die("Connection failed: %s\n", mysqli_connect_error()); $col = "c1"; $query = "SELECT $col FROM t"; $result = $mysqli->query($query); while($row = $result->fetch_assoc()) { echo "<p>" . $row["$col"] . "</p>\n"; } $result->close(); $mysqli->close(); ?>
Assembling an SQL statement in this fashion is sometimes known as “Dynamic SQL”.
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.
expr
:expr
ORexpr
|expr
||expr
|expr
XORexpr
|expr
ANDexpr
|expr
&&expr
| NOTexpr
| !expr
|boolean_primary
IS [NOT] {TRUE | FALSE | UNKNOWN} |boolean_primary
boolean_primary
:boolean_primary
IS [NOT] NULL |boolean_primary
<=>predicate
|boolean_primary
comparison_operator
predicate
|boolean_primary
comparison_operator
{ALL | ANY} (subquery
) |predicate
comparison_operator
: = | >= | > | <= | < | <> | !=predicate
:bit_expr
[NOT] IN (subquery
) |bit_expr
[NOT] IN (expr
[,expr
] ...) |bit_expr
[NOT] BETWEENbit_expr
ANDpredicate
|bit_expr
SOUNDS LIKEbit_expr
|bit_expr
[NOT] LIKEsimple_expr
[ESCAPEsimple_expr
] |bit_expr
[NOT] REGEXPbit_expr
|bit_expr
bit_expr
:bit_expr
|bit_expr
|bit_expr
&bit_expr
|bit_expr
<<bit_expr
|bit_expr
>>bit_expr
|bit_expr
+bit_expr
|bit_expr
-bit_expr
|bit_expr
*bit_expr
|bit_expr
/bit_expr
|bit_expr
DIVbit_expr
|bit_expr
MODbit_expr
|bit_expr
%bit_expr
|bit_expr
^bit_expr
|bit_expr
+interval_expr
|bit_expr
-interval_expr
|simple_expr
simple_expr
:literal
|identifier
|function_call
|simple_expr
COLLATEcollation_name
|param_marker
|variable
|simple_expr
||simple_expr
| +simple_expr
| -simple_expr
| ~simple_expr
| !simple_expr
| BINARYsimple_expr
| (expr
[,expr
] ...) | ROW (expr
,expr
[,expr
] ...) | (subquery
) | EXISTS (subquery
) | {identifier
expr
} |match_expr
|case_expr
|interval_expr
Notes:
For operator precedence, see in Section 12.3.1, “Operator Precedence”.
For literal value syntax, see Section 9.1, “Literal Values”.
For identifier syntax, see Section 9.2, “Schema Object Names”.
Variables can be user variables, system variables, or stored program local variables or parameters:
User variables: Section 9.4, “User-Defined Variables”
System variables: Section 5.1.5, “Using System Variables”
Local variables: Section 13.6.4.1, “Local Variable DECLARE Syntax”
Parameters: Section 13.1.16, “CREATE PROCEDURE and CREATE FUNCTION Syntax”
param_marker
is ?
as
used in prepared statements for placeholders. See
Section 13.5.1, “PREPARE Syntax”.
(
indicates
a subquery that returns a single value; that is, a scalar
subquery. See Section 13.2.10.1, “The Subquery as Scalar Operand”.
subquery
)
{
is ODBC escape syntax
and is accepted for ODBC compatibility. The value is
identifier
expr
}expr
. The curly braces in the syntax
should be written literally; they are not metasyntax as used
elsewhere in syntax descriptions.
match_expr
indicates a
MATCH
expression. See
Section 12.9, “Full-Text Search Functions”.
case_expr
indicates a
CASE
expression. See
Section 12.4, “Control Flow Functions”.
interval_expr
represents a time
interval. The syntax is INTERVAL
, where
expr
unit
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.
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 a
multiple-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
TEMPORARY
keyword in the following comment is
executed only by servers from MySQL 3.23.02 or higher:
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
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:
SELECT /*+ BKA(t1) */ FROM ... ;
For more information, see Section 8.9.3, “Optimizer Hints”.
The use of short-form mysql commands such as
\C
within multiple-line /* ...
*/
comments is not supported.