这里是Innodb 文件结构分析的预备知识，主要介绍数据字典表；在mysql 8中.frm 的数据字典文件已经消失，所有的数据文件都已经存储Innodb 的数据表中；在 information_schema 中存在数个表面看是数据字典表（实际是视图）,
Mysql-HuoSi->show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLLATIONS |
| COLUMN_PRIVILEGES |
| COLUMNS |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_LOCK_WAITS |
| INNODB_LOCKS |
| INNODB_METRICS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_DATAFILES |
| INNODB_SYS_FIELDS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_TABLESPACES |
| INNODB_SYS_TABLESTATS |
| INNODB_SYS_VIRTUAL |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMA_PRIVILEGES |
| SCHEMATA |
| SESSION_STATUS |
| SESSION_VARIABLES |
| SHOW_STATISTICS |
| SHOW_STATISTICS_DYNAMIC |
| STATISTICS |
| STATISTICS_BASE |
| STATISTICS_DYNAMIC |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TABLES |
| TABLES_DYNAMIC |
| TABLESPACES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
67 rows in set (0.00 sec)
在这里可以看起定义：
Mysql-HuoSi->show create table COLUMNS \G
*************************** 1. row ***************************
View: COLUMNS
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `COLUMNS` AS select (`cat`.`name` collate utf8_tolower_ci) AS `TABLE_CATALOG`,(`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,(`col`.`name` collate utf8_tolower_ci) AS `COLUMN_NAME`,`col`.`ordinal_position` AS `ORDINAL_POSITION`,`col`.`default_value_utf8` AS `COLUMN_DEFAULT`,if((`col`.`is_nullable` = 1),'YES','NO') AS `IS_NULLABLE`,substring_index(substring_index(`col`.`column_type_utf8`,'(',1),' ',1) AS `DATA_TYPE`,internal_dd_char_length(`col`.`type`,`col`.`char_length`,`coll`.`name`,0) AS `CHARACTER_MAXIMUM_LENGTH`,internal_dd_char_length(`col`.`type`,`col`.`char_length`,`coll`.`name`,1) AS `CHARACTER_OCTET_LENGTH`,if((`col`.`numeric_precision` = 0),NULL,`col`.`numeric_precision`) AS `NUMERIC_PRECISION`,if(((`col`.`numeric_scale` = 0) and (`col`.`numeric_precision` = 0)),NULL,`col`.`numeric_scale`) AS `NUMERIC_SCALE`,`col`.`datetime_precision` AS `DATETIME_PRECISION`,(case `col`.`type` when 'MYSQL_TYPE_STRING' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_SET' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) else NULL end) AS `CHARACTER_SET_NAME`,(case `col`.`type` when 'MYSQL_TYPE_STRING' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_SET' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) else NULL end) AS `COLLATION_NAME`,`col`.`column_type_utf8` AS `COLUMN_TYPE`,`col`.`column_key` AS `COLUMN_KEY`,if((ifnull(`col`.`generation_expression_utf8`,'IS_NOT_GC') = 'IS_NOT_GC'),if((`col`.`is_auto_increment` = TRUE),concat(ifnull(concat('on update ',`col`.`update_option`,' '),''),'auto_increment'),ifnull(concat('on update ',`col`.`update_option`),'')),convert(if(`col`.`is_virtual`,'VIRTUAL GENERATED','STORED GENERATED') using utf8)) AS `EXTRA`,get_dd_column_privileges(`sch`.`name`,`tbl`.`name`,`col`.`name`) AS `PRIVILEGES`,ifnull(`col`.`comment`,'') AS `COLUMN_COMMENT`,ifnull(`col`.`generation_expression_utf8`,'') AS `GENERATION_EXPRESSION` from (((((`mysql`.`columns` `col` join `mysql`.`tables` `tbl` on((`col`.`table_id` = `tbl`.`id`))) join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `coll` on((`col`.`collation_id` = `coll`.`id`))) join `mysql`.`character_sets` `cs` on((`coll`.`character_set_id` = `cs`.`id`))) where (internal_get_view_warning_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`) and can_access_column(`sch`.`name`,`tbl`.`name`,`col`.`name`) and (not(`tbl`.`hidden`)))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)
从这里可以看到这个视图的源表为 mysql.xxxx,那么我们直接去访问下，相关的表看看：
Mysql-HuoSi->use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Mysql-HuoSi->show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| procs_priv |
| proxies_priv |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
32 rows in set (0.00 sec)
这里可以看到，并没有想要看的表，如果直接查询呢：
Mysql-HuoSi->select * from mysql.columns;
ERROR 3554 (HY000): Access to system table 'mysql.columns' is rejected.
这里会报错，那么这些表到底存不存在呢，答案是肯定的：
[HuSi@HuoSi]mysql $ pwd
/Volumes/ssd/hadoop/soft/mysql-8/mysql8/data/mysql
[HuSi@HuoSi]mysql $ ls
catalogs.ibd help_relation.ibd slow_log.CSM
character_sets.ibd help_topic.ibd slow_log.CSV
collations.ibd index_column_usage.ibd slow_log_103.SDI
column_stats.ibd index_partitions.ibd st_spatial_reference_systems.ibd
column_type_elements.ibd index_stats.ibd table_partition_values.ibd
columns.ibd indexes.ibd table_partitions.ibd
columns_priv.ibd innodb_index_stats.ibd table_stats.ibd
component.ibd innodb_table_stats.ibd tables.ibd
db.ibd parameter_type_elements.ibd tables_priv.ibd
default_roles.ibd parameters.ibd tablespace_files.ibd
engine_cost.ibd plugin.ibd tablespaces.ibd
events.ibd procs_priv.ibd time_zone.ibd
foreign_key_column_usage.ibd proxies_priv.ibd time_zone_leap_second.ibd
foreign_keys.ibd role_edges.ibd time_zone_name.ibd
func.ibd routines.ibd time_zone_transition.ibd
general_log.CSM schemata.ibd time_zone_transition_type.ibd
general_log.CSV server_cost.ibd triggers.ibd
general_log_102.SDI servers.ibd user.ibd
gtid_executed.ibd slave_master_info.ibd version.ibd
help_category.ibd slave_relay_log_info.ibd view_routine_usage.ibd
help_keyword.ibd slave_worker_info.ibd view_table_usage.ibd
[HuSi@HuoSi]mysql $
这里可以看到对应的 数据文件是存在的，那么之所以隐藏数据字典表的目的应该是防止直接对这些表的修改，从而导致的数据库错误；那么这些表可以查看吗，答案是可以，但是要将启动方式改为debug的方式，下面来看一一看：
[HuSi@HuoSi]mysql8 $ bin/mysqld-debug --defaults-file=./my.cnf &
[1] 4270
然后登陆数据库：
[HuSi@HuoSi]mysql8 $ bin/mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr-debug-log MySQL Community Server - Debug (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET SESSION debug='+d,skip_dd_table_access_check';
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show create table columns;
+---------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------+
| columns | CREATE TABLE `columns` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`table_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci NOT NULL,
`ordinal_position` int(10) unsigned NOT NULL,
`type` enum('MYSQL_TYPE_DECIMAL','MYSQL_TYPE_TINY','MYSQL_TYPE_SHORT','MYSQL_TYPE_LONG','MYSQL_TYPE_FLOAT','MYSQL_TYPE_DOUBLE','MYSQL_TYPE_NULL','MYSQL_TYPE_TIMESTAMP','MYSQL_TYPE_LONGLONG','MYSQL_TYPE_INT24','MYSQL_TYPE_DATE','MYSQL_TYPE_TIME','MYSQL_TYPE_DATETIME','MYSQL_TYPE_YEAR','MYSQL_TYPE_NEWDATE','MYSQL_TYPE_VARCHAR','MYSQL_TYPE_BIT','MYSQL_TYPE_TIMESTAMP2','MYSQL_TYPE_DATETIME2','MYSQL_TYPE_TIME2','MYSQL_TYPE_NEWDECIMAL','MYSQL_TYPE_ENUM','MYSQL_TYPE_SET','MYSQL_TYPE_TINY_BLOB','MYSQL_TYPE_MEDIUM_BLOB','MYSQL_TYPE_LONG_BLOB','MYSQL_TYPE_BLOB','MYSQL_TYPE_VAR_STRING','MYSQL_TYPE_STRING','MYSQL_TYPE_GEOMETRY','MYSQL_TYPE_JSON') COLLATE utf8_bin NOT NULL,
`is_nullable` tinyint(1) NOT NULL,
`is_zerofill` tinyint(1) DEFAULT NULL,
`is_unsigned` tinyint(1) DEFAULT NULL,
`char_length` int(10) unsigned DEFAULT NULL,
`numeric_precision` int(10) unsigned DEFAULT NULL,
`numeric_scale` int(10) unsigned DEFAULT NULL,
`datetime_precision` int(10) unsigned DEFAULT NULL,
`collation_id` bigint(20) unsigned DEFAULT NULL,
`has_no_default` tinyint(1) DEFAULT NULL,
`default_value` blob,
`default_value_utf8` text COLLATE utf8_bin,
`default_option` blob,
`update_option` varchar(32) COLLATE utf8_bin DEFAULT NULL,
`is_auto_increment` tinyint(1) DEFAULT NULL,
`is_virtual` tinyint(1) DEFAULT NULL,
`generation_expression` longblob,
`generation_expression_utf8` longtext COLLATE utf8_bin,
`comment` varchar(2048) COLLATE utf8_bin NOT NULL,
`hidden` tinyint(1) NOT NULL,
`options` mediumtext COLLATE utf8_bin,
`se_private_data` mediumtext COLLATE utf8_bin,
`column_key` enum('','PRI','UNI','MUL') COLLATE utf8_bin NOT NULL,
`column_type_utf8` mediumtext COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `table_id` (`table_id`,`name`),
UNIQUE KEY `table_id_2` (`table_id`,`ordinal_position`),
KEY `collation_id` (`collation_id`),
CONSTRAINT `columns_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`),
CONSTRAINT `columns_ibfk_2` FOREIGN KEY (`collation_id`) REFERENCES `collations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3464 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC |
+---------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这里就可以看到具体的表了；那么在解析数据字典表的定义对解析 innodb的数据文件有什么帮助呢？这里做下说明，假如没有数据字典的定义，当拿到一个表的数据文件时，其具体的record 记录的存储格式为： variable length–null Column—extr byte—rowid–transaction id–roll back segment pointer—fields ；如果所有的列都是定长且不为空，那么fields 这里可能会造成无法判断其具体的长度，从而无法解析具体的数据；所以知道数据字典表的具体定义后可以，可以将对应表的定义找到，从而方便解析数据；数据库隐藏的表共有：
catalogs
character_sets
collations
columns
foreign_key_column_usage
foreign_keys
index_column_usage
indexes
index_stats
schemata
tables
table_stats
其实当一个表发生损坏时，一般不影响查看表的定义，这里可以作为一种预备知识，当数据库损坏比较严重时，以备不时之需，实际用处很少，只是一个兴趣方面的研究罢了；
