Documentation Home
MySQL 5.6 参考手册
MySQL 8.0 参考手册  / 第 26 章 INFORMATION_SCHEMA 表  / 26.3 INFORMATION_SCHEMA 总表  /  21.3.19 INFORMATION_SCHEMA ROUTINES 表

21.3.19 INFORMATION_SCHEMA ROUTINES 表

该ROUTINES表提供有关存储例程（存储过程和存储函数）的信息。该ROUTINES表不包括内置（本机）函数或可加载函数。

名为“ mysql.procName ”的列 表示与表列（如果有）mysql.proc对应的INFORMATION_SCHEMA ROUTINES表列。

该ROUTINES表有以下列：

  • SPECIFIC_NAME

    例程的名称。

  • ROUTINE_CATALOG

    例程所属目录的名称。该值始终为def.

  • ROUTINE_SCHEMA

    例程所属的模式（数据库）的名称。

  • ROUTINE_NAME

    例程的名称。

  • ROUTINE_TYPE

    PROCEDURE对于存储过程， FUNCTION对于存储函数。

  • DATA_TYPE

    如果例程是存储函数，返回值数据类型。如果例程是存储过程，则此值为空。

    该DATA_TYPE值只是类型名称，没有其他信息。该 DTD_IDENTIFIER值包含类型名称和可能的其他信息，例如精度或长度。

  • CHARACTER_MAXIMUM_LENGTH

    对于存储的函数字符串返回值，最大字符长度。如果例程是存储过程，则此值为NULL.

  • CHARACTER_OCTET_LENGTH

    对于存储的函数字符串返回值，以字节为单位的最大长度。如果例程是存储过程，则此值为 NULL.

  • NUMERIC_PRECISION

    对于存储函数数字返回值，数字精度。如果例程是存储过程，则此值为 NULL.

  • NUMERIC_SCALE

    对于存储的函数数字返回值，数字比例。如果例程是存储过程，则此值为 NULL.

  • DATETIME_PRECISION

    对于存储函数时间返回值，小数秒精度。如果例程是存储过程，则此值为NULL.

  • CHARACTER_SET_NAME

    对于存储的函数字符串返回值，字符集名称。如果例程是存储过程，则此值为NULL.

  • COLLATION_NAME

    对于存储的函数字符串返回值，排序规则名称。如果例程是存储过程，则此值为NULL.

  • DTD_IDENTIFIER

    如果例程是存储函数，返回值数据类型。如果例程是存储过程，则此值为空。

    该DATA_TYPE值只是类型名称，没有其他信息。该 DTD_IDENTIFIER值包含类型名称和可能的其他信息，例如精度或长度。

  • ROUTINE_BODY

    The language used for the routine definition. This value is always SQL.

  • ROUTINE_DEFINITION

    The text of the SQL statement executed by the routine.

  • EXTERNAL_NAME

    This value is always NULL.

  • EXTERNAL_LANGUAGE

    The language of the stored routine. MySQL calculates EXTERNAL_LANGUAGE thus:

    • If mysql.proc.language='SQL', EXTERNAL_LANGUAGE is NULL

    • Otherwise, EXTERNAL_LANGUAGE is what is in mysql.proc.language. However, we do not have external languages yet, so it is always NULL.

  • PARAMETER_STYLE

    This value is always SQL.

  • IS_DETERMINISTIC

    YES or NO, depending on whether the routine is defined with the DETERMINISTIC characteristic.

  • SQL_DATA_ACCESS

    The data access characteristic for the routine. The value is one of CONTAINS SQL, NO SQL, READS SQL DATA, or MODIFIES SQL DATA.

  • SQL_PATH

    This value is always NULL.

  • SECURITY_TYPE

    The routine SQL SECURITY characteristic. The value is one of DEFINER or INVOKER.

  • CREATED

    The date and time when the routine was created. This is a TIMESTAMP value.

  • LAST_ALTERED

    The date and time when the routine was last modified. This is a TIMESTAMP value. If the routine has not been modified since its creation, this value is the same as the CREATED value.

  • SQL_MODE

    The SQL mode in effect when the routine was created or altered, and under which the routine executes. For the permitted values, see Section 5.1.10, “Server SQL Modes”.

  • ROUTINE_COMMENT

    The text of the comment, if the routine has one. If not, this value is empty.

  • DEFINER

    The account named in the DEFINER clause (often the user who created the routine), in 'user_name'@'host_name' format.

  • CHARACTER_SET_CLIENT

    The session value of the character_set_client system variable when the routine was created.

  • COLLATION_CONNECTION

    The session value of the collation_connection system variable when the routine was created.

  • DATABASE_COLLATION

    The collation of the database with which the routine is associated.

Notes

  • 要查看有关例程的信息，您必须是例程DEFINER子句中指定的用户或有权 SELECT访问该 mysql.proc表。如果您没有例程本身的权限，则为该 ROUTINE_DEFINITION列显示的值为 NULL。

  • 表中还提供了有关存储的函数返回值的信息PARAMETERS 。存储函数的返回值行可以标识为 ORDINAL_POSITION值为 0 的行。