MySQL :: MySQL 8.4 参考手册 :: 28.3.8 INFORMATION

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 28 个 INFORMATION_SCHEMA 表 / 28.3 INFORMATION_SCHEMA 总表 / 28.3.8 INFORMATION_SCHEMA COLUMNS 表

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 71%

28.3.8 信息模式 COLUMNS 表28.3.8 The INFORMATION_SCHEMA COLUMNS Table

COLUMNS 表提供关于表中列的信息。相关的 ST_GEOMETRY_COLUMNS 表提供存储空间数据的表列的信息。请参见第 28.3.35 节，“INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS 表”。The COLUMNS table provides information about columns in tables. The related ST_GEOMETRY_COLUMNS table provides information about table columns that store spatial data. See Section 28.3.35, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table”.

COLUMNS 表包含以下列：The COLUMNS table has these columns:

TABLE_CATALOG
包含该列的表所属的目录名称。此值始终为 def。The name of the catalog to which the table containing the column belongs. This value is always def.
TABLE_SCHEMA
包含该列的表所属的模式（数据库）名称。The name of the schema (database) to which the table containing the column belongs.
TABLE_NAMETABLE_NAME
包含该列的表名称。The name of the table containing the column.
COLUMN_NAMECOLUMN_NAME
列名称。The name of the column.
ORDINAL_POSITION
列在表中的位置。ORDINAL_POSITION 是必要的，因为你可能想要说 ORDER BY ORDINAL_POSITION。与 SHOW COLUMNS 不同，从 SELECT 查询 COLUMNS 表不会自动排序。The position of the column within the table. ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION. Unlike SHOW COLUMNS, SELECT from the COLUMNS table does not have automatic ordering.
COLUMN_DEFAULT
列的默认值。如果该列有明确的 NULL 默认值，或者列定义中没有包含 DEFAULT 子句，则此值为 NULL。The default value for the column. This is NULL if the column has an explicit default of NULL, or if the column definition includes no DEFAULT clause.
IS_NULLABLE
列是否可为空。如果列可以存储NULL值，则值为YES；如果不可以，则值为NO。The column nullability. The value is YES if NULL values can be stored in the column, NO if not.
DATA_TYPE
列的数据类型。The column data type.
DATA_TYPE的值仅为类型名称，不包含其他信息。COLUMN_TYPE的值包含类型名称，并可能包含其他信息，如精度或长度。The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.
CHARACTER_MAXIMUM_LENGTH
对于字符串列，最大字符长度。For string columns, the maximum length in characters.
CHARACTER_OCTET_LENGTH
对于字符串列，最大字节长度。For string columns, the maximum length in bytes.
NUMERIC_PRECISION
对于数值列，数值精度。For numeric columns, the numeric precision.
NUMERIC_SCALE
对于数值列，数值刻度。For numeric columns, the numeric scale.
DATETIME_PRECISION
对于时间列，小数秒精度。For temporal columns, the fractional seconds precision.
CHARACTER_SET_NAME
对于字符列，字符集名称。For character string columns, the character set name.
COLLATION_NAME
对于字符列，排序规则名称。For character string columns, the collation name.
COLUMN_TYPE
列的数据类型。The column data type.
DATA_TYPE的值仅为类型名称，不包含其他信息。COLUMN_TYPE的值包含类型名称，并可能包含其他信息，如精度或长度。The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.
COLUMN_KEY
该列是否有索引：Whether the column is indexed:
- 如果 COLUMN_KEY 为空，则该列没有索引，或仅作为多列非唯一索引中的次要列被索引。If COLUMN_KEY is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.
- 如果 COLUMN_KEY 为 PRI，则该列是 PRIMARY KEY，或是多列 PRIMARY KEY 中的一列。If COLUMN_KEY is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.
- 如果 COLUMN_KEY 是 UNI，则该列是 UNIQUE 索引的第一列。（UNIQUE 索引允许多个 NULL 值，但您可以通过查看 Null 列来判断该列是否允许 NULL 值。）If COLUMN_KEY is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null column.)
- 如果 COLUMN_KEY 是 MUL，则该列是非唯一索引的第一列，在该列中允许给定值的多次出现。If COLUMN_KEY is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
如果一个表的某列适用多个 COLUMN_KEY 值，COLUMN_KEY 会显示优先级最高的一个，优先级顺序为 PRI、UNI、MUL。If more than one of the COLUMN_KEY values applies to a given column of a table, COLUMN_KEY displays the one with the highest priority, in the order PRI, UNI, MUL.
如果唯一索引不能包含 NULL 值，并且表中没有 PRIMARY KEY，则 UNIQUE 索引可能显示为 PRI。如果多个列形成复合 UNIQUE 索引，UNIQUE 索引可能显示为 MUL；虽然这些列的组合是唯一的，但每列仍可以包含给定值的多个出现。A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.
EXTRA
有关给定列的任何额外信息。在以下情况下，该值非空：Any additional information that is available about a given column. The value is nonempty in these cases:
- 对于具有 AUTO_INCREMENT 属性的列，其值为 auto_increment。auto_increment for columns that have the AUTO_INCREMENT attribute.
- on update CURRENT_TIMESTAMPon update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.
- STORED GENERATED 或 VIRTUAL GENERATED 用于生成列。STORED GENERATED or VIRTUAL GENERATED for generated columns.
- DEFAULT_GENERATED 用于具有表达式默认值的列。DEFAULT_GENERATED for columns that have an expression default value.
PRIVILEGESPRIVILEGES
您对该列拥有的权限。The privileges you have for the column.
COLUMN_COMMENT
列定义中包含的任何注释。Any comment included in the column definition.
GENERATION_EXPRESSION
对于生成列，显示用于计算列值的表达式。非生成列为空。有关生成列的信息，请参见第 15.1.20.8 节，“CREATE TABLE 与生成列”。For generated columns, displays the expression used to compute column values. Empty for nongenerated columns. For information about generated columns, see Section 15.1.20.8, “CREATE TABLE and Generated Columns”.
SRS_ID
该值适用于空间列。它包含列的 SRID 值，该值指示存储在列中的值的空间参考系统。请参阅第 13.4.1 节，“空间数据类型”，以及第 13.4.5 节，“空间参考系统支持”。对于非空间列以及没有 SRID 属性的空间列，该值为 NULL。This value applies to spatial columns. It contains the column SRID value that indicates the spatial reference system for values stored in the column. See Section 13.4.1, “Spatial Data Types”, and Section 13.4.5, “Spatial Reference System Support”. The value is NULL for nonspatial columns and spatial columns with no SRID attribute.

注意事项Notes

在 SHOW COLUMNS 中，Type 显示包括来自几个不同 COLUMNS 列的值。In SHOW COLUMNS, the Type display includes values from several different COLUMNS columns.
CHARACTER_OCTET_LENGTH 应该与 CHARACTER_MAXIMUM_LENGTH 相同，除非是多字节字符集。CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multibyte character sets.
CHARACTER_SET_NAME 可以从 COLLATION_NAME 推导出来。例如，如果你执行 SHOW FULL COLUMNS FROM t，并且在 COLLATION_NAME 列看到一个值为 utf8mb4_swedish_ci，那么字符集就是第一个下划线前的部分：utf8mb4。CHARACTER_SET_NAME can be derived from COLLATION_NAME. For example, if you say SHOW FULL COLUMNS FROM t, and you see in the COLLATION_NAME column a value of utf8mb4_swedish_ci, the character set is what appears before the first underscore: utf8mb4.

列信息也可以通过 SHOW COLUMNS 语句获取。请参见第 15.7.7.6 节，“SHOW COLUMNS 语句”。以下语句几乎等价：Column information is also available from the SHOW COLUMNS statement. See Section 15.7.7.6, “SHOW COLUMNS Statement”. The following statements are nearly equivalent:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

SHOW COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE 'wild']

关于生成的不可见主键列的信息默认在此表中可见。您可以通过设置 show_gipk_in_create_table_and_information_schema = OFF 来使这些信息隐藏。有关更多信息，请参见第 15.1.20.11 节，“生成的不可见主键”。Information about generated invisible primary key columns is visible in this table by default. You can cause such information to be hidden by setting show_gipk_in_create_table_and_information_schema = OFF. For more information, see Section 15.1.20.11, “Generated Invisible Primary Keys”.

首页上一级下一节