9.2 Schema Object Names

 mysql> SELECT * FROM `select` WHERE `select`.id > 100;

 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)

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

 mysql> SELECT 1 AS `one`, 2 AS 'two';
 +-----+-----+
 | one | two |
 +-----+-----+
 |   1 |   2 |
 +-----+-----+

9.2.1 Identifier Length Limits

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

Identifier Maximum Type Length (characters)

Database 64 (*note 'NDB': mysql-cluster. storage engine: 63)

Table 64 (*note 'NDB': mysql-cluster. storage engine: 63)

Column 64

Index 64

Constraint 64

Stored 64 Program

View 64

Tablespace 64

Server 64

Log File 64 Group

Alias 256 (see exception following table)

Compound 16 Statement
Label

User-Defined64 Variable

Aliases for column names in *note 'CREATE VIEW': create-view. statements are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).

For constraint definitions that include no constraint name, the server internally generates a name derived from the associated table name. For example, internally generated foreign key constraint names consist of the table name plus 'ibfk' and a number. If the table name is close to the length limit for constraint names, the additional characters required for the constraint name may cause that name to exceed the limit, resulting in an error.

Identifiers are stored using Unicode (UTF-8). This applies to identifiers in table definitions that are stored in '.frm' files and to identifiers stored in the grant tables in the 'mysql' database. The sizes of the identifier string columns in the grant tables are measured in characters. You can use multibyte characters without reducing the number of characters permitted for values stored in these columns.

NDB Cluster imposes a maximum length of 63 characters for names of databases and tables. See *note mysql-cluster-limitations-database-objects::.

Values such as user name and host names in MySQL account names are strings rather than identifiers. For information about the maximum length of such values as stored in grant tables, see *note grant-tables-scope-column-properties::.

 File: manual.info.tmp, Node: identifier-qualifiers, Next: identifier-case-sensitivity, Prev: identifier-length, Up: identifiers

9.2.2 Identifier Qualifiers

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

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

 CREATE TABLE t1 (i INT);

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

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

 CREATE TABLE db1.t1 (i INT);

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

Qualifiers have these characteristics:

• An unqualified name consists of a single identifier. A qualified name consists of multiple identifiers.

• The components of a multiple-part name must be separated by period ('.') characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which to interpret the final identifier.

• The qualifier character is a separate token and need not be contiguous with the associated identifiers. For example, TBL_NAME.COL_NAME and TBL_NAME . COL_NAME are equivalent.

• If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, write 'my-table.my-column', not 'my-table.my-column'.

• A reserved word that follows a period in a qualified name must be an identifier, so in that context it need not be quoted.

• The syntax '.TBL_NAME' means the table TBL_NAME in the default database.

  Note:

  This syntax is deprecated as of MySQL 5.7.20; expect it to be removed in a future version of MySQL.

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

• A database name is fully qualified and takes no qualifier:

    CREATE DATABASE db1;

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

    CREATE TABLE mytable ...;
    CREATE VIEW myview ...;
    CREATE PROCEDURE myproc ...;
    CREATE FUNCTION myfunc ...;
    CREATE EVENT myevent ...;
  
    CREATE TABLE mydb.mytable ...;
    CREATE VIEW mydb.myview ...;
    CREATE PROCEDURE mydb.myproc ...;
    CREATE FUNCTION mydb.myfunc ...;
    CREATE EVENT mydb.myevent ...;

• A trigger is associated with a table, so any qualifier applies to the table name:

    CREATE TRIGGER mytrigger ... ON mytable ...;
  
    CREATE TRIGGER mytrigger ... ON mydb.mytable ...;

• A column name may be given multiple qualifiers to indicate context in statements that reference it, as shown in the following table.

  Column Reference Meaning

  COL_NAME Column COL_NAME from whichever table used in the statement contains a column of that name

  TBL_NAME.COL_NAME Column COL_NAME from table TBL_NAME of the default database

  DB_NAME.TBL_NAME.COL_NAME Column COL_NAME from table TBL_NAME of the database DB_NAME

  In other words, a column name may be given a table-name qualifier, which itself may be given a database-name qualifier. Examples of unqualified and qualified column references in 'SELECT' statements:

    SELECT c1 FROM mytable
    WHERE c2 > 100;
  
    SELECT mytable.c1 FROM mytable
    WHERE mytable.c2 > 100;
  
    SELECT mydb.mytable.c1 FROM mydb.mytable
    WHERE mydb.mytable.c2 > 100;

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

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

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

 SELECT c1, c2, db1.t.c FROM db1.t INNER JOIN db2.t
 WHERE db2.t.c > 100;

Table aliases enable qualified column references to be written more simply:

 SELECT c1, c2, t1.c FROM db1.t AS t1 INNER JOIN db2.t AS t2
 WHERE t2.c > 100;

 File: manual.info.tmp, Node: identifier-case-sensitivity, Next: identifier-mapping, Prev: identifier-qualifiers, Up: identifiers

9.2.3 Identifier Case Sensitivity

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix. See *note extensions-to-ansi::. The 'lower_case_table_names' system variable also affects how the server handles identifier case sensitivity, as described later in this section.

Note:

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

 mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Column, index, stored routine, and event names are not case-sensitive on any platform, nor are column aliases.

However, names of logfile groups are case-sensitive. This differs from standard SQL.

By default, table aliases are case-sensitive on Unix, but not so on Windows or macOS. The following statement would not work on Unix, because it refers to the alias both as 'a' and as 'A':

 mysql> SELECT COL_NAME FROM TBL_NAME AS a
        WHERE a.COL_NAME = 1 OR A.COL_NAME = 2;

However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

How table and database names are stored on disk and used in MySQL is affected by the 'lower_case_table_names' system variable, which you can set when starting *note 'mysqld': mysqld. 'lower_case_table_names' can take the values shown in the following table. This variable does not affect case sensitivity of trigger identifiers. On Unix, the default value of 'lower_case_table_names' is 0. On Windows, the default value is 1. On macOS, the default value is 2.

Value Meaning

'0' Table and database names are stored on disk using the lettercase specified in the note 'CREATE TABLE': create-table. or note 'CREATE DATABASE': 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 macOS). If you force this variable to 0 with '--lower-case-table-names=0' on a case-insensitive file system and access 'MyISAM' tablenames using different lettercases, index corruption may result.

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

'2' Table and database names are stored on disk using the lettercase specified in the note 'CREATE TABLE': create-table. or note 'CREATE DATABASE': 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 and view names are stored in lowercase, as for 'lower_case_table_names=1'.

If you are using MySQL on only one platform, you do not normally have to change the 'lower_case_table_names' variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named 'my_table' and 'MY_TABLE', but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

• Use 'lower_case_table_names=1' on all systems. The main disadvantage with this is that when you use note 'SHOW TABLES': show-tables. or note 'SHOW DATABASES': 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 note 'mysqld': mysqld. and restarting it with the new variable setting. To do this for an individual table, use note 'RENAME TABLE': rename-table.:

 RENAME TABLE T1 TO t1;

To convert one or more entire databases, dump them before setting 'lower_case_table_names', then drop the databases, and reload them after setting 'lower_case_table_names':

1. Use *note 'mysqldump': mysqldump. to dump each database:

     mysqldump --databases db1 > db1.sql
     mysqldump --databases db2 > db2.sql
     ...

   Do this for each database that must be recreated.

2. Use 'DROP DATABASE' to drop each database.

3. Stop the server, set 'lower_case_table_names', and restart the server.

4. Reload the dump file for each database. Because 'lower_case_table_names' is set, each database and table name is converted to lowercase as it is re-created:

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

Object names may be considered duplicates if their uppercase forms are equal according to a binary collation. That is true for names of cursors, conditions, procedures, functions, savepoints, stored routine parameters, stored program local variables, and plugins. It is not true for names of columns, constraints, databases, partitions, statements prepared with *note 'PREPARE': prepare, tables, triggers, users, and user-defined variables.

File system case sensitivity can affect searches in string columns of 'INFORMATION_SCHEMA' tables. For more information, see *note charset-collation-information-schema::.

 File: manual.info.tmp, Node: identifier-mapping, Next: function-resolution, Prev: identifier-case-sensitivity, Up: identifiers

9.2.4 Mapping of Identifiers to File Names

There is a correspondence between database and table identifiers and names in the file system. For the basic structure, MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory. For the table format files ('.FRM'), the data is always stored in this structure and location.

For the data and index files, the exact representation on disk is storage engine specific. These files may be stored in the same location as the 'FRM' files, or the information may be stored in a separate file. 'InnoDB' data is stored in the InnoDB data files. If you are using tablespaces with 'InnoDB', then the specific tablespace files you create are used instead.

Any character is legal in database or table identifiers except ASCII NUL ('X'00''). MySQL encodes any characters that are problematic in the corresponding file system objects when it creates database directories or table files:

• Basic Latin letters ('a..zA..Z'), digits ('0..9') and underscore ('_') are encoded as is. Consequently, their case sensitivity directly depends on file system features.

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

  Code Pattern Number Used Unused Blocks Range

  00C0..017F

  [@][0..4][g..z]5*20= 10097 3 Latin-1 Supplement + Latin Extended-A

  0370..03FF

  [@][5..9][g..z]5*20= 10088 12 Greek and Coptic

  0400..052F

  [@][g..z][0..6]20*7= 140137 3 Cyrillic + Cyrillic Supplement

  0530..058F

  [@][g..z][7..8]20*2= 40 38 2 Armenian

  2160..217F

  [@][g..z][9]20*1= 20 16 4 Number Forms

  0180..02AF

  [@][g..z][a..k]20*11=220203 17 Latin Extended-B + IPA Extensions

  1E00..1EFF

  [@][g..z][l..r]20*7= 140136 4 Latin Extended Additional

  1F00..1FFF

  [@][g..z][s..z]20*8= 160144 16 Greek Extended

  .... ....

  [@][a..f][g..z]6*20= 1200 120 RESERVED

  24B6..24E9

  [@][@][a..z]26 26 0 Enclosed Alphanumerics

  FF21..FF5A

  [@][a..z][@]26 26 0 Halfwidth and Fullwidth forms

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

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

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

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

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

On Windows, some names such as 'nul', 'prn', and 'aux' are encoded by appending '@@@' to the name when the server creates the corresponding file or directory. This occurs on all platforms for portability of the corresponding database object between platforms.

If you have databases or tables from a version of MySQL older than 5.1.6 that contain special characters and for which the underlying directory names or file names have not been updated to use the new encoding, the server displays their names with a prefix of '#mysql50#' in the output from 'INFORMATION_SCHEMA' tables or note 'SHOW': show. statements. For example, if you have a table named 'a@b' and its name encoding has not been updated, note 'SHOW TABLES': show-tables. displays it like this:

 mysql> SHOW TABLES;
 +----------------+
 | Tables_in_test |
 +----------------+
 | #mysql50#a@b   |
 +----------------+

To refer to such a name for which the encoding has not been updated, you must supply the '#mysql50#' prefix:

 mysql> SHOW COLUMNS FROM `a@b`;
 ERROR 1146 (42S02): Table 'test.a@b' doesn't exist

 mysql> SHOW COLUMNS FROM `#mysql50#a@b`;
 +-------+---------+------+-----+---------+-------+
 | Field | Type    | Null | Key | Default | Extra |
 +-------+---------+------+-----+---------+-------+
 | i     | int(11) | YES  |     | NULL    |       |
 +-------+---------+------+-----+---------+-------+

To update old names to eliminate the need to use the special prefix to refer to them, re-encode them with *note 'mysqlcheck': mysqlcheck. The following commands update all names to the new encoding:

 mysqlcheck --check-upgrade --all-databases
 mysqlcheck --fix-db-names --fix-table-names --all-databases

To check only specific databases or tables, omit '--all-databases' and provide the appropriate database or table arguments. For information about note 'mysqlcheck': mysqlcheck. invocation syntax, see note mysqlcheck::.

Note:

The '#mysql50#' prefix is intended only to be used internally by the server. You should not create databases or tables with names that use this prefix.

Also, note 'mysqlcheck': mysqlcheck. cannot fix names that contain literal instances of the '@' character that is used for encoding special characters. If you have databases or tables that contain this character, use note 'mysqldump': mysqldump. to dump them before upgrading to MySQL 5.1.6 or later, and then reload the dump file after upgrading.

Note:

Conversion of pre-MySQL 5.1 database names containing special characters to 5.1 format with the addition of a '#mysql50#' prefix is deprecated; expect it to be removed in a future version of MySQL. Because such conversions are deprecated, the '--fix-db-names' and '--fix-table-names' options for note 'mysqlcheck': mysqlcheck. and the 'UPGRADE DATA DIRECTORY NAME' clause for the note 'ALTER DATABASE': alter-database. statement are also deprecated.

Upgrades are supported only from one release series to another (for example, 5.0 to 5.1, or 5.1 to 5.5), so there should be little remaining need for conversion of older 5.0 database names to current versions of MySQL. As a workaround, upgrade a MySQL 5.0 installation to MySQL 5.1 before upgrading to a more recent release.

 File: manual.info.tmp, Node: function-resolution, Prev: identifier-mapping, Up: identifiers

9.2.5 Function Name Parsing and Resolution

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.

• *note function-name-parsing::

• *note function-name-resolution::

Built-In Function Name Parsing

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the 'IGNORE_SPACE' SQL mode.

When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name. For example, in the following statements, the first reference to 'count' is a function call, whereas the second reference is a table name:

 SELECT COUNT(*) FROM mytable;
 CREATE TABLE count (i INT);

The parser should recognize the name of a built-in function as indicating a function call only when parsing what is expected to be an expression. That is, in nonexpression context, function names are permitted as identifiers.

However, some built-in functions have special parsing or implementation considerations, so the parser uses the following rules by default to distinguish whether their names are being used as function calls or as identifiers in nonexpression context:

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

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

The requirement that function calls be written with no whitespace between the name and the parenthesis applies only to the built-in functions that have special considerations. 'COUNT' is one such name. The 'sql/lex.h' source file lists the names of these special functions for which following whitespace determines their interpretation: names defined by the 'SYM_FN()' macro in the 'symbols[]' array.

The following list names the functions in MySQL 5.7 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: 'FUNC_NAME ()' is interpreted as a built-in function if there is one with the given name; if not, 'FUNC_NAME ()' is interpreted as a loadable function or stored function if one exists with that name.

The 'IGNORE_SPACE' SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive:

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

    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 *note keywords::). 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 *note sql-mode::, 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 by 'IGNORE_SPACE'), so they fail with or without whitespace following the name if 'IGNORE_SPACE' is enabled:

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

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

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

Function Name Resolution

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

• Built-in functions and loadable functions

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

• Built-in functions and stored functions

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

• Loadable functions and stored functions

  Loadable functions and stored functions share the same namespace, so you cannot create a loadable function 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 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 note 'DROP FUNCTION': drop-function. to drop the loadable function and note 'CREATE FUNCTION': create-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 with the same name as an existing stored function, you have two choices: Rename the stored function to use a nonconflicting name, or change calls to the function so that they use a schema qualifier (that is, use 'SCHEMA_NAME.FUNC_NAME()' syntax). In either case, modify any affected code accordingly.

 File: manual.info.tmp, Node: keywords, Next: user-variables, Prev: identifiers, Up: language-structure