MySQL supports built-in (native) functions, loadable functions, 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.
The parser uses default rules for parsing names of built-in
functions. These rules can be changed by enabling the
IGNORE_SPACE
SQL mode.
When the parser encounters a word that is the name of a
built-in function, it must determine whether the name
signifies a function call or is instead a nonexpression
reference to an identifier such as a table or column name. For
example, in the following statements, the first reference to
count
is a function call, whereas the
second reference is a table name:
SELECT COUNT(*) FROM mytable;
CREATE TABLE count (i INT);
The parser should recognize the name of a built-in function as indicating a function call only when parsing what is expected to be an expression. That is, in nonexpression context, function names are permitted as identifiers.
However, some built-in functions have special parsing or implementation considerations, so the parser uses the following rules by default to distinguish whether their names are being used as function calls or as identifiers in nonexpression context:
To use the name as a function call in an expression, there must be no whitespace between the name and the following
(
parenthesis character.Conversely, to use the function name as an identifier, it must not be followed immediately by a parenthesis.
The requirement that function calls be written with no
whitespace between the name and the parenthesis applies only
to the built-in functions that have special considerations.
COUNT
is one such name. The
sql/lex.h
source file lists the names of
these special functions for which following whitespace
determines their interpretation: names defined by the
SYM_FN()
macro in the
symbols[]
array.
The following list names the functions in MySQL
8.4 that are affected by the
IGNORE_SPACE
setting and
listed as special in the sql/lex.h
source
file. You may find it easiest to treat the no-whitespace
requirement as applying to all function calls.
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 loadable 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 11.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, withIGNORE_SPACE
enabled, both of the following statements fail with a syntax error because the parser interpretscount
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 7.1.11, “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 loadable functions 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 byIGNORE_SPACE
), so they fail with or without whitespace following the name ifIGNORE_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);
The following rules describe how the server resolves references to function names for function creation and invocation:
Built-in functions and loadable functions
An error occurs if you try to create a loadable function with the same name as a built-in function.
IF NOT EXISTS
has no effect in such cases. See Section 15.7.4.1, “CREATE FUNCTION Statement for Loadable Functions”, for more information.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 thetest
schema, invoke it astest.PI()
because the server resolvesPI()
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 withSHOW WARNINGS
.IF NOT EXISTS
has no effect in such cases; see Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”.Loadable functions and stored functions
It is possible to create a stored function with the same name as an existing loadable function, or the other way around. The server generates a warning if a proposed stored function name collides with an existing loadable function name, or if a proposed loadable function name would be the same as that of an existing stored function. In either case, once both functions exist, it is necessary thereafter to qualify the stored function with a schema name when invoking it; the server assumes in such cases that the unqualified name refers to the loadable function.
MySQL 8.4 supports
IF NOT EXISTS
withCREATE FUNCTION
statements, but it has no effect in such cases.
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 loadable function with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the loadable function becomes inaccessible. To correct this, use
DROP FUNCTION
to drop the loadable function andCREATE FUNCTION
to re-create the loadable function 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 or loadable 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 any calls to the function that do not do so already to use a schema qualifier (
syntax). In either case, modify any affected code accordingly.schema_name
.func_name
()