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
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.
As of MySQL 4.1.1, 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 multi-byte characters. Character value ordering is a function of the string collation.
Also as of MySQL 4.1.1, 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 9.1.3.5, “Character String Literal Character Set and Collation”, and Section 9.1.3.6, “National Character Set”.
Within a string, certain sequences have special meaning. Each of
these sequences begins with a backslash
(“\
”), known as the
escape character. MySQL recognizes the
following escape sequences.
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
”.
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
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 9.1.3.5, “Character String Literal Character Set and Collation”.
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 11.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), the following
characters must be represented by escape sequences.
Character | Escape Sequence |
---|---|
NUL | NUL byte (0x00 ). Represent this
character by “\0 ” (a
backslash followed by an ASCII
“0 ” character). |
\ | Backslash (ASCII 92). Represent this character by
“\\ ”. |
' | Single quote (ASCII 39). Represent this character by
“\' ”. |
" | Double quote (ASCII 34). Represent this character by
“\" ”. |
When writing application programs, any string that might contain any of these special characters must be properly escaped before the string is used as a data value in an SQL statement that is sent to the MySQL server. You can do this in two ways:
Process the string with a function that escapes the special
characters. In a C program, you can use the
mysql_real_escape_string()
C
API function to escape characters. See
Section 17.6.6.51, “mysql_real_escape_string()”. The Perl DBI
interface provides a quote
method to
convert special characters to the proper escape sequences.
See Section 17.8, “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.
Integers are represented as a sequence of digits. Floats use
“.
” as a decimal separator.
Either type of number may be preceded by
“-
” or
“+
” to indicate a negative or
positive value, respectively
Examples of valid integers:
1221 0 -32
Examples of valid floating-point numbers:
294.42 -32032.6809e+10 148.00
An integer may be used in a floating-point context; it is interpreted as the equivalent floating-point number.
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 permits temporal literals to be specified using a type keyword and a string. The space between the keyword and string is optional.
DATE 'str
' TIME 'str
' TIMESTAMP 'str
'
MySQL recognizes those constructions and also the corresponding ODBC syntax:
{ d 'str
' } { t 'str
' } { ts 'str
' }
However, MySQL ignores the type keyword and each of the
preceding constructions produces the string value
'
, with a type
of str
'VARCHAR
.
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.
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. Although this fractional part is recognized,
it is discarded from values stored into
DATETIME
or
TIMESTAMP
columns. For
information about fractional seconds support in MySQL, see
Section 10.3.4, “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 10.3.6, “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. Although this fractional part
is recognized, it is discarded from values stored into
TIME
columns. For information
about fractional seconds support in MySQL, see
Section 10.3.4, “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 0x0a+0;
-> 10 mysql>SELECT 0x5061756c;
-> 'Paul'
In MySQL 4.1 (and in MySQL 4.0 when using the
--new
option), 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 0x41, CAST(0x41 AS UNSIGNED);
-> 'A', 65
The X'
syntax is new in 4.0 and is based on standard SQL. The
hexstring
'0x
syntax is based on ODBC. Hexadecimal
strings are often used by ODBC to supply values for
BLOB
columns.
Beginning with MySQL 4.0.1, you can convert a string or a number
to a string in hexadecimal format with the
HEX()
function:
mysql>SELECT HEX('cat');
-> '636174' mysql>SELECT 0x636174;
-> 'cat'
Beginning with MySQL 4.1, 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
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 12.2.5, “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.5.3, “Problems with NULL Values”.
Database, table, index, column, and alias names are identifiers. This section describes the permissible syntax for identifiers in MySQL. Section 8.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. The
set of alphanumeric characters from the current character set,
“_
”, and
“$
” are not special. Reserved
words are listed at Section 8.3, “Reserved Words”. (Exception:
A reserved word that follows a period in a qualified name must be
an identifier, so it need not be quoted.)
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.6, “Server SQL Modes”.
As of MySQL 4.1, 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, 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 |
+-----+-----+
Identifier quoting was introduced in MySQL 3.23.6 to permit use of
identifiers that contain special characters or are reserved words.
Before 3.23.6, you cannot use identifiers that require quotation
marks, so the only legal characters are the set of alphanumeric
characters from the current character set,
“_
”, and
“$
”.
Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal.
Identifiers may begin with a digit but unless quoted may not consist solely of digits.
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 8.4, “User-Defined Variables”, for more information and examples of workarounds.
There are some restrictions on the characters that may appear in identifiers:
No identifier can contain ASCII NUL (0x00
)
or a byte with a value of 255.
Before MySQL 4.1, identifier quote characters should not be used in identifiers.
Database, table, and column names should not end with space characters.
Database and table names cannot contain
“/
”,
“\
”,
“.
”, or characters that are
not permitted in file names.
The following table describes the maximum length for each type of identifier. Before MySQL 4.1.5, the maximum-length restrictions on identifiers are measured in bytes, not characters. Until that version, if you use multi-byte characters in your identifier names, the maximum length will depend on the byte count of all the characters used.
Identifier | Maximum Length |
---|---|
Database | 64 |
Table | 64 |
Column | 64 |
Index | 64 |
Constraint | 64 |
Alias | 256 |
Beginning with MySQL 4.1, 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 multi-byte
characters without reducing the number of characters permitted for
values stored in these columns, something not true prior to MySQL
4.1. The permissible Unicode characters are those in the Basic
Multilingual Plane (BMP). Supplementary characters are not
permitted.
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 . This syntax is
unavailable before MySQL 3.22. |
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). Consequently, the case
sensitivity of the underlying operating system plays a part in
the case sensitivity of database and table names. This means
database and table names are not case sensitive in Windows, and
case sensitive in most varieties of Unix. One notable exception
is Mac OS X, which is Unix-based but uses a default file system
type (HFS+) that is not case sensitive. However, Mac OS X also
supports UFS volumes, which are case sensitive just as on any
Unix. See Section 1.9.4, “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 and table names are not case sensitive on
some platforms, you should not refer to a given database or
table 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 and index names are not case sensitive on any platform, nor are column aliases.
Table aliases are case sensitive before MySQL 4.1.1. The
following query would not work 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;
If you have trouble remembering the permissible lettercase for database and table names, 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. On Unix, the default
value of lower_case_table_names
is 0. On Windows, the default value is 1. On Mac OS X, the
default is 1 before MySQL 4.0.18 and 2 as of 4.0.18.
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 Mac
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, this may lead to index corruption. |
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 as of MySQL 4.0.2, and to table aliases as of 4.1.1. |
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 . Setting
lower_case_table_names
to 2 can be done as of MySQL 4.0.18. |
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 those 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.
MySQL 4.1 supports built-in (native) functions and user-defined functions (UDFs). 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 exact list of
function names for which following whitespace determines their
interpretation are those listed in the
sql_functions[]
array of the
sql/lex.h
source file. Before MySQL 5.1,
they are rather numerous (about 200), so you may find it easiest
to treat the no-whitespace requirement as applying to all
function calls. In MySQL 5.1, parser improvements reduce to
about 30 the number of affected function names.
For functions not listed in the
sql_functions[]
) array, 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 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 8.3, “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.6, “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 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 server resolves references to function names for function
creation and invocation as follows: A UDF can be created with
the same name as a built-in function but the UDF cannot be
invoked because the parser resolves invocations of the function
to refer to the built-in function. For example, if you create a
UDF named ABS
, references to
ABS()
invoke the built-in
function.
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 then use
CREATE FUNCTION
to re-create the
UDF with a different nonconflicting name.
Certain words 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.
Reserved words are permitted as identifiers if you quote them as described in Section 8.2, “Database, Table, Index, Column, and Alias 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)' 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 detail on
recognition of function names, see
Section 8.2.3, “Function Name Parsing and Resolution”.
The words in the following table are explicitly reserved in MySQL
4.1. 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 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. A reserved word
can be used as an identifier if you quote it.
ADD | ALL | ALTER |
ANALYZE | AND | AS |
ASC | BEFORE | BETWEEN |
BIGINT | BINARY | BLOB |
BOTH | BY | CASCADE |
CASE | CHANGE | CHAR |
CHARACTER | CHECK | COLLATE |
COLUMN | COLUMNS | CONSTRAINT |
CONVERT | CREATE | CROSS |
CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP |
CURRENT_USER | DATABASE | DATABASES |
DAY_HOUR | DAY_MICROSECOND | DAY_MINUTE |
DAY_SECOND | DEC | DECIMAL |
DEFAULT | DELAYED | DELETE |
DESC | DESCRIBE | DISTINCT |
DISTINCTROW | DIV | DOUBLE |
DROP | DUAL | ELSE |
ENCLOSED | ESCAPED | EXISTS |
EXPLAIN | FALSE | FIELDS |
FLOAT | FLOAT4 | FLOAT8 |
FOR | FORCE | FOREIGN |
FROM | FULLTEXT | GRANT |
GROUP | HAVING | HIGH_PRIORITY |
HOUR_MICROSECOND | HOUR_MINUTE | HOUR_SECOND |
IF | IGNORE | IN |
INDEX | INFILE | INNER |
INSERT | INT | INT1 |
INT2 | INT3 | INT4 |
INT8 | INTEGER | INTERVAL |
INTO | IS | JOIN |
KEY | KEYS | KILL |
LEADING | LEFT | LIKE |
LIMIT | LINES | LOAD |
LOCALTIME | LOCALTIMESTAMP | LOCK |
LONG | LONGBLOB | LONGTEXT |
LOW_PRIORITY | MATCH | MEDIUMBLOB |
MEDIUMINT | MEDIUMTEXT | MIDDLEINT |
MINUTE_MICROSECOND | MINUTE_SECOND | MOD |
NATURAL | NOT | NO_WRITE_TO_BINLOG |
NULL | NUMERIC | ON |
OPTIMIZE | OPTION | OPTIONALLY |
OR | ORDER | OUTER |
OUTFILE | PRECISION | PRIMARY |
PRIVILEGES | PROCEDURE | PURGE |
READ | REAL | REFERENCES |
REGEXP | RENAME | REPLACE |
REQUIRE | RESTRICT | REVOKE |
RIGHT | RLIKE | SECOND_MICROSECOND |
SELECT | SEPARATOR | SET |
SHOW | SMALLINT | SONAME |
SPATIAL | SQL_BIG_RESULT | SQL_CALC_FOUND_ROWS |
SQL_SMALL_RESULT | SSL | STARTING |
STRAIGHT_JOIN | TABLE | TABLES |
TERMINATED | THEN | TINYBLOB |
TINYINT | TINYTEXT | TO |
TRAILING | TRUE | UNION |
UNIQUE | UNLOCK | UNSIGNED |
UPDATE | USAGE | USE |
USING | UTC_DATE | UTC_TIME |
UTC_TIMESTAMP | VALUES | VARBINARY |
VARCHAR | VARCHARACTER | VARYING |
WHEN | WHERE | WITH |
WRITE | XOR | YEAR_MONTH |
ZEROFILL |
The following are new reserved words in MySQL 4.0:
CHECK | FORCE | LOCALTIME |
LOCALTIMESTAMP | REQUIRE | SQL_CALC_FOUND_ROWS |
SSL | XOR |
The following are new reserved words in MySQL 4.1:
BEFORE | COLLATE | CONVERT |
CURRENT_USER | DAY_MICROSECOND | DIV |
DUAL | FALSE | HOUR_MICROSECOND |
MINUTE_MICROSECOND | MOD | NO_WRITE_TO_BINLOG |
SECOND_MICROSECOND | SEPARATOR | SPATIAL |
TRUE | UTC_DATE | UTC_TIME |
UTC_TIMESTAMP | VARCHARACTER |
MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:
MySQL supports user variables as of version 3.23.6. 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-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.
User variables are written as
@
, where the
variable name var_name
var_name
consists of
alphanumeric characters from the current character set,
“.
”,
“_
”, 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`
).
The default character set is latin1
(cp1252
West European). This can be changed with the
--default-character-set
option to
mysqld. See
Section 9.6, “Character Set Configuration”.
User variable names are not case sensitive in MySQL 5.0 and up, but are case sensitive before MySQL 5.0.
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.
Beginning with MySQL 4.1.1, 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” as of MySQL 4.1.11 and 5.0.3. (This is the same coercibility as table column values.)
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, you should never assign a value to a user
variable and read the value within the same statement. You might
get the results you expect, but this is not guaranteed. The order
of evaluation for expressions involving user variables is
undefined and may change based on the elements contained within a
given statement. In SELECT @a, @a:=@a+1, ...
,
you might think that MySQL will evaluate @a
first and then do an assignment second. However, changing the
statement (for example, by adding a GROUP BY
,
HAVING
, or ORDER BY
clause)
may cause MySQL to select an execution plan with a different order
of evaluation.
Another issue with assigning a value to a variable and reading the value within the same 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 that if you are constructing a string for use as a prepared statement to be executed 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 12.6, “SQL Syntax for Prepared Statements”, for more information.
PREPARE
is available as of MySQL
4.1. Before MySQL 4.1, you must assemble a string for the query in
application code, 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. Operator precedence is given
in Section 11.3.1, “Operator Precedence”.
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 literal value syntax, see Section 8.1, “Literal Values”.
For identifier syntax, see Section 8.2, “Database, Table, Index, Column, and Alias Names”.
Variables can be user variables, system variables, or stored program local variables or parameters:
User variables: Section 8.4, “User-Defined Variables”
System variables: Section 5.1.4, “Using System Variables”
Local variables: Local Variable DECLARE Syntax
Parameters: CREATE PROCEDURE and CREATE FUNCTION Syntax
param_marker
is '?'
as used in prepared statements for placeholders. See
Section 12.6.1, “PREPARE Syntax”.
(
indicates
a subquery that returns a single value; that is, a scalar
subquery. See Section 12.2.8.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 11.9, “Full-Text Search Functions”.
case_expr
indicates a
CASE
expression. See
Section 11.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 11.7, “Date and Time Functions”.
The meaning of the ||
operator 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. See Section 5.1.6, “Server SQL Modes”.
MySQL Server supports three comment styles:
From a “#
” character to the
end of the line.
From a “--
” sequence to
the end of the line. This style is supported as of MySQL
3.23.3. 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.9.5.8, “'--' 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.
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.) However, there are some limitations on the way that
mysql parses /* ... */
comments:
A semicolon within the comment is taken to indicate the end of the current SQL statement and anything following it to indicate the beginning of the next statement. This problem was fixed in MySQL 4.0.13.
A single quote, double quote, or backtick character is taken
to indicate the beginning of a quoted string or identifier,
even within a comment. If the quote is not matched by a second
quote within the comment, the parser doesn't realize the
comment has ended. If you are running mysql
interactively, you can tell that it has gotten confused like
this because the prompt changes from
mysql>
to '>
,
">
, or `>
. This
problem was fixed in MySQL 4.1.1.
The use of short-form commands such as \C
within multi-line /* ... */
comments is not
supported.
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.
For affected versions of MySQL, these limitations apply both when
you run mysql interactively and when you put
commands in a file and use mysql in batch mode
to process the file with mysql <
file_name
.