MySQL :: MySQL 8.4 参考手册 :: 28.4.29 INFORMATION_SCHEMA 的 INNODB

MySQL 8.4 参考手册 · 中文镜像

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

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

MySQL 8.4 参考手册 / 28 个 INFORMATION_SCHEMA 表 / 28.4 INFORMATION_SCHEMA InnoDB 表 / 28.4.29 INFORMATION_SCHEMA 的 INNODB_VIRTUAL 表

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

28.4.29 INFORMATION_SCHEMA INNODB_VIRTUAL 表28.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table

INNODB_VIRTUAL 表提供有关 InnoDB 虚拟生成列以及基于虚拟生成列的列的元数据。The INNODB_VIRTUAL table provides metadata about InnoDB virtual generated columns and columns upon which virtual generated columns are based.

对于基于某一虚拟生成列的每一列，INNODB_VIRTUAL 表中会出现一行。A row appears in the INNODB_VIRTUAL table for each column upon which a virtual generated column is based.

INNODB_VIRTUAL 表具有以下列：The INNODB_VIRTUAL table has these columns:

TABLE_ID
表示与虚拟列关联的表的标识符；与 INNODB_TABLES.TABLE_ID 相同的值。An identifier representing the table associated with the virtual column; the same value as INNODB_TABLES.TABLE_ID.
POS
The position value of the virtual generated column. The value is large because it encodes the column sequence number and ordinal position. The formula used to calculate the value uses a bitwise operation:
```
((nth virtual generated column for the InnoDB instance + 1) << 16)
+ the ordinal position of the virtual generated column
```
例如，如果 InnoDB 实例中的第一个虚拟生成列是表的第三列，则公式为 (0 + 1) << 16) + 2。InnoDB 实例中的第一个虚拟生成列始终为编号 0。作为表中的第三列，虚拟生成列的序数位置为 2。序数位置从 0 开始计数。For example, if the first virtual generated column in the InnoDB instance is the third column of the table, the formula is (0 + 1) << 16) + 2. The first virtual generated column in the InnoDB instance is always number 0. As the third column in the table, the ordinal position of the virtual generated column is 2. Ordinal positions are counted from 0.
BASE_POS
虚拟生成列所基于的列的序数位置。The ordinal position of the columns upon which a virtual generated column is based.

示例Example

mysql> CREATE TABLE `t1` (
         `a` int(11) DEFAULT NULL,
         `b` int(11) DEFAULT NULL,
         `c` int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
         `h` varchar(10) DEFAULT NULL
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_VIRTUAL
       WHERE TABLE_ID IN
         (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES
          WHERE NAME LIKE "test/t1");
+----------+-------+----------+
| TABLE_ID | POS   | BASE_POS |
+----------+-------+----------+
|       98 | 65538 |        0 |
|       98 | 65538 |        1 |
+----------+-------+----------+

注意事项Notes

如果将一个常量值分配给一个虚拟生成列，如下面的表所示，该列的条目不会出现在INNODB_VIRTUAL表中。要显示条目，虚拟生成列必须有一个基础列。If a constant value is assigned to a virtual generated column, as in the following table, an entry for the column does not appear in the INNODB_VIRTUAL table. For an entry to appear, a virtual generated column must have a base column.
```
CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (5) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
然而，此类列的元数据会出现在INNODB_COLUMNS表中。However, metadata for such a column does appear in the INNODB_COLUMNS table.
你必须拥有 PROCESS 权限才能查询此表。You must have the PROCESS privilege to query this table.
使用 INFORMATION_SCHEMA COLUMNS 表或 SHOW COLUMNS 语句查看关于此表列的更多信息，包括数据类型和默认值。Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

首页上一级下一节