INNODB_SYS_FOREIGN - xiaoboluo768/mysql-system-schema GitHub Wiki

该表提供查询有关InnoDB外键的元数据信息，等同于InnoDB数据字典中SYS_FOREIGN表的信息

该表为memory引擎临时表，查询该表的用户需要有process权限

表定义语句

CREATE TEMPORARY TABLE `INNODB_SYS_FOREIGN` (
  `ID` varchar(193) NOT NULL DEFAULT '',
  `FOR_NAME` varchar(193) NOT NULL DEFAULT '',
  `REF_NAME` varchar(193) NOT NULL DEFAULT '',
  `N_COLS` int(11) unsigned NOT NULL DEFAULT '0',
  `TYPE` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8

表字段含义

ID：外键约束的名称（注意不是数值，该字符串是约束名称，不是索引名称），该ID值由外键名称字符串+数据库名称组成，例如："test/products_fk"

FOR_NAME：外键相关的子表的名称(即外键索引列所在的表)

REF_NAME：外键相关的父表的名称(即外键索引列引用列所在的表)

N_COLS：外键索引中的列数量

TYPE：带有关于外键列的信息的位标志集合，1 = ON DELETE CASCADE、2 = ON UPDATE SET NULL、4 = ON UPDATE CASCADE、8 = ON UPDATE SET NULL、16 = ON DELETE NO ACTION、32 = ON UPDATE NO ACTION

表记录内容示例

root@localhost : (none) 11:44:09> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
+-------------------------------+------------------------+-----------------------+--------+------+
| ID                            | FOR_NAME              | REF_NAME              | N_COLS | TYPE |
+-------------------------------+------------------------+-----------------------+--------+------+
| employees/dept_emp_ibfk_1    | employees/dept_emp    | employees/employees  |      1 |    1 |
| employees/dept_emp_ibfk_2    | employees/dept_emp    | employees/departments |      1 |    1 |
| employees/dept_manager_ibfk_1 | employees/dept_manager | employees/employees  |      1 |    1 |
| employees/dept_manager_ibfk_2 | employees/dept_manager | employees/departments |      1 |    1 |
| employees/salaries_ibfk_1    | employees/salaries    | employees/employees  |      1 |    1 |
| employees/titles_ibfk_1      | employees/titles      | employees/employees  |      1 |    1 |
+-------------------------------+------------------------+-----------------------+--------+------+
6 rows in set (0.00 sec)

上一篇：INNODB_SYS_COLUMNS表 |下一篇：INNODB_SYS_TABLESTATS表

INNODB_SYS_FOREIGN - xiaoboluo768/mysql-system-schema GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️