MySQL8.0自带工具ibd2sdi解析ibd文件
ibd文件是mysql的表数据文件，在mysql8之前数据表里的表定义放在xxx.frm文件中，表数据和表索引放在.ibd文件中。都在mysql的data目录下的数据库名所在目录下。不知道自己data目录在哪里的在数据库执行show variables like ‘%datadir%’ 查看路径
将MySQL8.0的bin路径加入系统环境变量path里（C:\Program Files\MySQL\MySQL Server 8.0\bin）
进入mysql数据文件目录C:\ProgramData\MySQL\MySQL Server 8.0\Data\test>
找到自己的表的ibd文件
在当前目录打开cmd
执行命令解析
ibd2sdi --dump-file member.txt member.ibd
查看解析后的txt文件，我们可以看到8.0版本的mysql把表结构、索引全部都放到了表数据文件ibd文件中了
["ibd2sdi"
,
{
"type": 1,
"id": 401,
"object":
{
"mysqld_version_id": 80031,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "member",
"mysql_version_id": 80031,
"created": 20230110035139,
"last_altered": 20230110035139,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": true,
"is_auto_increment": true,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 10,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAA==",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "ID",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1094;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int unsigned",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "username",
"type": 16,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 64,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAA=",
"default_value_utf8_null": false,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "账户名",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1094;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(16)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "password",
"type": 16,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 3,
"char_length": 96,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==",
"default_value_utf8_null": false,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "密码",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1094;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(24)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "createTime",
"type": 19,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 4,
"char_length": 19,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 0,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "gAAAAAA=",
"default_value_utf8_null": false,
"default_value_utf8": "CURRENT_TIMESTAMP",
"default_option": "CURRENT_TIMESTAMP",
"update_option": "",
"comment": "创建时间",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1094;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "datetime",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
},
{
"name": "updateTime",
"type": 19,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 5,
"char_length": 19,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 0,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "gAAAAAA=",
"default_value_utf8_null": false,
"default_value_utf8": "CURRENT_TIMESTAMP",
"default_option": "CURRENT_TIMESTAMP",
"update_option": "CURRENT_TIMESTAMP",
"comment": "修改时间",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1094;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "datetime",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
},
{
"name": "DB_TRX_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 6,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1094;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_ROLL_PTR",
"type": 9,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 7,
"char_length": 7,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1094;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "test",
"se_private_id": 1094,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "会员",
"se_private_data": "autoinc=0;version=0;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"row_format": 2,
"partition_type": 0,
"partition_expression": "",
"partition_expression_utf8": "",
"default_partitioning": 0,
"subpartition_type": 0,
"subpartition_expression": "",
"subpartition_expression_utf8": "",
"default_subpartitioning": 0,
"indexes": [
{
"name": "PRIMARY",
"hidden": false,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=213;root=4;space_id=32;table_id=1094;trx_id=1646;",
"type": 1,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4,
"order": 2,
"hidden": false,
"column_opx": 0
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 5
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 6
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
},
{
"ordinal_position": 5,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 2
},
{
"ordinal_position": 6,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
},
{
"ordinal_position": 7,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 4
}
],
"tablespace_ref": "test/member"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 255
}
}
}
,
{
"type": 2,
"id": 37,
"object":
{
"mysqld_version_id": 80031,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "test/member",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=32;server_version=80031;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": ".\\test\\member.ibd",
"se_private_data": "id=32;"
}
]
}
}
}
]
本文来自博客园，作者：HumorChen99，转载请注明原文链接：https://www.cnblogs.com/HumorChen/p/18039463
