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
User-defined and system variables
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 (‘
double quote (‘
'a string' "another string"
ANSI_QUOTES SQL mode is enabled,
string literals can be quoted only within single quotes because
a string quoted within double quotes is interpreted as an
A binary string is a string of bytes that has no character set or collation. A non-binary 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 non-binary strings the unit is the character and some character sets allow multi-byte characters. Character value ordering is a function of the string collation.
String literals may have an optional character set introducer
string'; SELECT _latin1'
string' COLLATE latin1_danish_ci;
For more information about these forms of string syntax, see Section 10.3.5, “Character String Literal Character Set and Collation”.
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:
|An ASCII 0 (|
|A single quote (‘|
|A double quote (‘|
|A backspace character.|
|A newline (linefeed) character.|
|A carriage return character.|
|A tab character.|
|ASCII 26 (Control-Z). See note following the table.|
|A backslash (‘|
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
These sequences are case sensitive. For example,
\b’ is interpreted as a
backspace, but ‘
interpreted as ‘
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
Escape processing is done according to the character set
indicated by the
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.3.5, “Character String Literal Character Set and Collation”.
\_’ sequences are used to search
for literal instances of ‘
_’ in pattern-matching contexts
where they would otherwise be interpreted as wildcard
characters. See the description of the
operator in Section 12.3.1, “String Comparison Functions”. If
you use ‘
\_’ in non-pattern-matching
contexts, they evaluate to the strings
\_’, not to
There are several ways to include quote characters within a string:
'’ inside a string quoted
'’ may be written as
"’ inside a string quoted
"’ may be written as
Precede the quote character by an escape character
'’ inside a string quoted
"’ needs no special
treatment and need not be doubled or escaped. In the same
"’ inside a string
quoted with ‘
'’ needs no
SELECT statements demonstrate
how quoting and escaping work:
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
BLOB column), the following characters must
be represented by escape sequences:
|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 22.214.171.124, “
mysql_real_escape_string()”. The Perl DBI
interface provides a
quote method to
convert special characters to the proper escape sequences.
See Section 24.4, “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
+’ 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.
MySQL supports hexadecimal values. In numeric contexts, these act like integers (64-bit precision). In string contexts, these act like binary strings, where each pair of hex digits is converted to a character:
SELECT x'4D7953514C';-> 'MySQL' mysql>
SELECT 0xa+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):
SELECT 0x41, CAST(0x41 AS UNSIGNED);-> 'A', 65
syntax is based on standard SQL. The
syntax is based on ODBC. Hexadecimal strings are often used by
ODBC to supply values for
You can convert a string or a number to a string in hexadecimal
format with the
SELECT HEX('cat');-> '636174' mysql>
SELECT 0x636174;-> 'cat'
FALSE evaluate to
0, respectively. The constant names can be
written in any lettercase.
SELECT TRUE, true, FALSE, false;-> 1, 1, 0, 0
Bit-field values can be written using
value is a binary value written using
zeros and ones.
Bit-field notation is convenient for specifying values to be
CREATE TABLE t (b BIT(8));mysql>
INSERT INTO t SET b = b'11111111';mysql>
INSERT INTO t SET b = b'1010';+------+----------+----------+----------+ | b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) | +------+----------+----------+----------+ | 255 | 11111111 | 377 | FF | | 10 | 1010 | 12 | A | +------+----------+----------+----------+
NULL value means “no data.”
NULL can be written in any lettercase.
Be aware that the
NULL value is different
from values such as
0 for numeric types or
the empty string for string types. See
Section B.5.3, “Problems with
For text file import or export operations performed with
LOAD DATA INFILE or
SELECT ... INTO
NULL is represented by the
\N sequence. See Section 13.2.5, “
LOAD DATA INFILE Syntax”.
Database, table, index, column, and alias names are identifiers. This section describes the allowable syntax for identifiers in MySQL.
The following table describes the maximum length for each type of identifier.
|Identifier||Maximum Length (bytes)|
There are some restrictions on the characters that may appear in identifiers:
No identifier can contain ASCII 0 (
a byte with a value of 255.
The use of identifier quote characters in identifiers is permitted, although it is best to avoid doing so if possible.
Database, table, and column names should not end with space characters.
Before MySQL 5.1.6, database names cannot contain
.’, or characters that are not
allowed in a directory name.
Before MySQL 5.1.6, table names cannot contain
.’, or characters that are not
allowed in a filename.
The length of the identifier is in bytes, not characters. If you use multi-byte characters in your identifier names, then the maximum length will depend on the byte count of all the characters used.
As of MySQL 5.1.6, special characters in database and table names
are encoded in the corresponding filesystem names as described in
Section 9.2.3, “Mapping of Identifiers to Filenames”. If you have databases or
tables from an older version of MySQL that contain special
characters and that have not been updated to use the new encoding,
you will see their names displayed with a prefix of
#mysql50#. For information about referring to
such names or converting them to the newer encoding, see that
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 string columns in the grant tables (and in any other
tables) in MySQL 5.1 are given as number of
characters. This means that (unlike some earlier versions of
MySQL) you can use multi-byte characters without reducing the
number of characters allowed for values stored in these columns.
An identifier may be quoted or unquoted. If an identifier is a
reserved word or contains special characters, you
must quote it whenever you refer to it.
(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.) For a list of reserved words, see
Section 9.3, “Reserved Words”. Special characters are those
outside the set of alphanumeric characters from the current
character set, ‘
The identifier quote character is the backtick
SELECT * FROM `select` WHERE `select`.id > 100;
ANSI_QUOTES SQL mode is enabled, it is
also allowable to quote identifiers within double quotes:
CREATE TABLE "test" (col INT);ERROR 1064: You have an error in your SQL syntax. (...) mysql>
CREATE TABLE "test" (col INT);Query OK, 0 rows affected (0.00 sec)
Note: Because the
ANSI_QUOTES mode causes the
server to interpret double-quoted strings as identifiers, string
literals must be enclosed within single quotes when this mode is
enabled. They cannot be enclosed within double quotes.
The server SQL mode is controlled as described in Section 5.2.6, “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
CREATE TABLE `a``b` (`c"d` INT);
Identifiers may begin with a digit but unless quoted may not consist solely of digits.
It is recommended that you do not use names of the form
N are integers. For example, avoid
identifiers, because an expression such as
is ambiguous. Depending on context, it might be interpreted as the
1e + 3 or as the number
Be careful when using
MD5() to produce table
names because it can produce names in illegal or ambiguous formats
such as those just described.
MySQL allows names that consist of a single identifier or
multiple identifiers. The components of a multiple-part name
should 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 column using any of the following forms:
|The column |
|The column |
|The column |
If any components of a multiple-part name require quoting, quote
them individually rather than quoting the name as a whole. For
You need not specify a
db_name.tbl_name prefix for a column
reference in a statement unless the reference would be
ambiguous. Suppose that tables
t2 each contain a column
c, and you retrieve
c in a
SELECT statement that uses both
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
to indicate which table you mean. Similarly, to retrieve from a
t in database
and from a table
t in database
db2 in the same statement, you must refer to
columns in those tables as
A word that follows a period in a qualified name must be an identifier, so it is not necessary to quote it, even if it is a reserved word.
.tbl_name means the table
tbl_name in the default database.
This syntax is accepted for ODBC compatibility because some ODBC
programs prefix table names with a
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 determines the
case sensitivity of database and table names. This means
database and table names are case sensitive in most varieties of
Unix, and not case sensitive in Windows. One notable exception
is Mac OS X, which is Unix-based but uses a default filesystem
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.
Note: 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
SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column, index and stored routine names are not case sensitive on any platform, nor are column aliases. Trigger names are case sensitive.
By default, table aliases are case sensitive on Unix, but not so
on Windows or Mac OS X. The following statement would not work
on Unix, because it refers to the alias both as
a and as
col_name= 1 OR A.
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. 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 value is 2.
|Table and database names are stored on disk using the lettercase
specified in the |
|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.|
|Table and database names are stored on disk using the lettercase
specified in the |
If you are using MySQL on only one platform, you don't normally
have to change the
variable. However, you may encounter difficulties if you want to
transfer tables between platforms that differ in filesystem case
sensitivity. For example, on Unix, you can have two different
MY_TABLE, but on Windows these two names are
considered identical. To avoid data transfer problems stemming
from lettercase of database or table names, you have two
lower_case_table_names=1 on all
systems. The main disadvantage with this is that when you
SHOW TABLES or
DATABASES, you don't see the names in their
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, you should set
lower_case_table_names to 1 on all
platforms to force names to be converted to lowercase.
Note that 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 restarting mysqld
with the new variable setting.
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, functions, procedures,
savepoints, and routine local variables. It is not true for
names of columns, constraints, databases, partitions, statements
PREPARE, tables, triggers,
users, and user-defined variables.
There is a correspondence between database and table identifiers and names in the filesystem. MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory.
Before MySQL 5.1.6, there are some limitations on the characters
that can be used in identifiers for database objects that
correspond to filesystem objects. For example, pathname
separator characters are disallowed, and
.’ is disallowed because it
begins the extension for table files.
As of MySQL 5.1.6, any character is legal in database or table
identifiers except ASCII NUL (
encodes any characters that are problematic in the corresponding
filesystem objects when it creates database directories or table
Basic Latin letters (
a..zA..Z) and digits
0..9) are encoded as is. Consequently,
their case sensitivity directly depends on filesystem
All other national letters from alphabets that have uppercase/lowercase mapping are encoded as follows:
Code range Pattern Number Used Unused Blocks ----------------------------------------------------------------------------- 00C0..017F [@][0..4][g..z] 5*20= 100 97 3 Latin1 Supplement + Ext A 0370..03FF [@][5..9][g..z] 5*20= 100 88 12 Greek + Coptic 0400..052F [@][g..z][0..6] 20*7= 140 140 137 Cyrillic 0530..058F [@][g..z][7..8] 20*2= 40 38 2 Armenian 2160..217F [@][g..z] 20*1= 20 16 4 Number Forms 0180..02AF [@][g..z][a..k] 28*11=220 203 17 Latin Ext B + IPA 1E00..0EFF [@][g..z][l..r] 20*7= 140 136 4 Latin Additional Extended 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 Full Width forms
One of the bytes in the sequence encodes lettercase. For
LATIN CAPITAL LETTER A WITH
GRAVE is encoded as
LATIN SMALL LETTER A WITH GRAVE
is encoded as
@0g. Here the third byte
lettercase. (On a case-insensitive filesystem, 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 non-letter characters, as well as letters from alphabets
that do not have uppercase/lowercase mapping (such Hebrew)
are encoded using hexadecimal representation using lowercase
letters for hex digits
0x003F -> @003f 0xFFFF -> @ffff
The hexadecimal values corrrespond to character values in
ucs2 double-byte character set.
On Windows, some names such as
aux cannot be
used as filenames because they are reserved as device names. As
of MySQL 5.1.10, these are allowable names in MySQL. They 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 that have not
been updated to use the new encoding, you will see their names
displayed with a prefix of
#mysql50# in the
INFORMATION_SCHEMA tables or
SHOW statements. For example, if you have a
[email protected] and its name encoding has not
SHOW TABLES displays it like
SHOW TABLES;+----------------+ | Tables_in_test | +----------------+ | #mysql50#[email protected] | +----------------+
To refer to such a name for which the encoding has not been
updated, you must supply the
SHOW COLUMNS FROM `[email protected]`;ERROR 1146 (42S02): Table '[email protected]' doesn't exist mysql>
SHOW COLUMNS FROM `#mysql50#[email protected]`;+-------+---------+------+-----+---------+-------+ | 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 command updates all names to the new encoding:
mysqlcheck --check-upgrade --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 8.11, “mysqlcheck — A Table Maintenance and Repair Program”.
MySQL 5.1 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 non-expression 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 non-expression 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 non-expression 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
ASCII is one such name. However,
for these non-affected function names, interpretation may vary
in expression context:
interpreted as a built-in function if there is one; if not,
interpreted as a user-defined function or stored function if one
exists with that name.
IGNORE_SPACE SQL mode can be used to
modify how the parser treats function names that are
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
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);
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;
IGNORE_SPACE also has
the side effect that the parser treats the affected function
names as reserved words (see
Section 9.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
non-expression context unless it is quoted. For example,
IGNORE_SPACE enabled, both of the
following statements fail with a syntax error because the
count as a reserved
CREATE TABLE count(i INT); CREATE TABLE count (i INT);
To use the function name in non-expression 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
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.2.6, “SQL Modes”, to see which composite
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 non-expression context. For
example, these statements use
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 non-expression context, write it as a quoted identifier:
CREATE TABLE `count`(i INT); CREATE TABLE `count` (i INT);
The number of function names affected by
IGNORE_SPACE was reduced significantly in
MySQL 5.1.13, from about 200 to about 30. As of MySQL 5.1.13,
only the following functions are still affected by the
For earlier versions of MySQL, check the contents of the
sql_functions array in the
sql/lex.h source file to see which
functions are affected by
Incompatibility warning: The
change in MySQL 5.1.13 that reduces the number of function names
IGNORE_SPACE improves the
consistency of parser operation. However, it also introduces the
possibility of incompatibility for old SQL code that relies on
the following conditions:
IGNORE_SPACE is disabled.
The presence or absence of whitespace following a function
name is used to distinguish between a built-in function and
stored function that have the same name (for example,
For functions that are no longer affected by
IGNORE_SPACE as of MySQL 5.1.13, that
strategy no longer works. Either of the following approaches can
be used if you have code that is subject to the preceding
If a stored function has a name that conflicts with a
built-in function, refer to the stored function with a
schema name qualifier, regardless of whether whitespace is
present. For example, write
Alternatively, rename the stored function to use a non-conflicting name and change invocations of the function to use the new name.
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
As of MySQL 5.1.14, an error occurs if you try to create a
UDF with the same name as a built-in function. Before
5.1.14, 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.
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
you invoke it as
test.PI() because the
PI() as a reference to
the built-in function. As of 5.1.14, the server creates a
warning if the stored function name collides with a built-in
function name. The warning can be displayed with
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
FUNCTION to drop the UDF, and then use
CREATE FUNCTION to re-create the UDF with
a different non-conflicting 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
non-conflicting name, or change calls to the function so
that they use a schema qualifier (that is, use
Certain words such as
reserved and require special treatment for use as identifiers such
as table and column names. This may also be true for the names of
Reserved words are permitted as identifiers if you quote them as described in Section 9.2, “Identifiers”:
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:
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 allowed 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 non-function context. For further detail on
recognition of function names, see
Section 9.2.4, “Function Name Parsing and Resolution”.
The words in the following table are explicitly reserved in MySQL
5.1. At some point, you might upgrade to a higher
version, so it's 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.
The following are new reserved words in MySQL 5.1:
MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:
You can store a value in a user-defined variable and then refer to it later. 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
var_name may consist of
alphanumeric characters from the current character set,
$’. The default character set is
latin1 (cp1252 West European). This may be
changed with the
--character-set-server option to
mysqld. See Section 5.10.1, “The Character Set Used for Data and Sorting”. A
user variable name can contain other characters if you quote it as
a string or identifier (for example,
Note: User variable names are case sensitive before MySQL 5.0 and not case sensitive in MySQL 5.0 and up.
One way to set a user-defined variable is by issuing a
:= can be used as the assignment operator. The
expr assigned to each variable can
evaluate to an integer, real, string, or
value. However, if the value of the variable is selected in a
result set, it is returned to the client as a string.
You can also assign a value to a user variable in statements other
SET. In this case, the assignment operator
:= and not
= is treated as a comparison operator in
SET @t1=0, @t2=0, @t3=0;mysql>
SELECT @t1:=(@t2:=1)[email protected]:=4,@t1,@t2,@t3;+----------------------+------+------+------+ | @t1:=(@t2:=1)[email protected]:=4 | @t1 | @t2 | @t3 | +----------------------+------+------+------+ | 5 | 5 | 1 | 4 | +----------------------+------+------+------+
User variables may be used in contexts where expressions are
allowed. This does not currently include contexts that explicitly
require a literal value, such as in the
clause of a
SELECT statement, or the
clause of a
LOAD DATA statement.
If a user variable is assigned a 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.)
Note: In a
SELECT statement, each expression is evaluated
only when sent to the client. This means that in a
GROUP BY, or
ORDER BY clause, you cannot refer to an
expression that involves variables that are set in the
SELECT list. For example, the following
statement does not work as expected:
SELECT (@aa:=id) AS a, (@aa+3) AS b FROM
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
from the previous selected row, not from the current row.
The order of evaluation for user variables is undefined and may
change based on the elements contained within a given query. In
SELECT @a, @a := @a+1 ..., you might think that
MySQL will evaluate
@a first and then do an
assignment second, but changing the query (for example, by adding
ORDER BY clause) may change the order of
The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed.
Another issue with setting a variable and using it in the same statement is that the default result type of a variable is based on the type of the variable at the start of the statement. The following example illustrates this:
SELECT @a,(@a:=20) FROM
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
@a is regarded as a number
for the next statement.
To avoid problems with this behavior, either do not set and use
the same variable within a single statement, or else set the
'' to define its type before you use it.
If you refer to a variable that has not been initialized, it has a
NULL and a type of string.
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 126.96.36.199, “'
--' as the Start of a Comment”.
/* sequence to the following
*/ sequence, as in the C programming
language. This syntax allows 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:
SELECT 1+1; # This comment continues to the end of linemysql>
SELECT 1+1; -- This comment continues to the end of linemysql>
SELECT 1 /* this is an in-line comment */ + 1;mysql>
this is a
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:
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
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.)