[MYSQL] 5.7能否从ibdata1中提取出表DDL

-- mysql 5.7的系统表DDL
-- 参考
-- dict/dict0crea.cc
-- include/dict0boot.h
-- dict/dict0boot.cc
-- dict/dict0dict.cc
-- ...

-- index_id 1
drop table if exists sys_tables;
create table sys_tables(
	name varchar(654),
	id bigint unsigned,
	n_cols int,
	`type` int unsigned,
	mix_id bigint unsigned,
	mix_len int unsigned,
	cluster_name varchar(255),
	space int unsigned,
	primary key(name)
) engine=innodb row_format=redundant charset=latin1;

-- index_id 2
drop table if exists sys_columns;
create table sys_columns(
	table_id bigint unsigned,
	pos int unsigned,
	name varchar(255),
	mtype int unsigned, -- main data type
	prtype int unsigned, -- precise type: charset(16 bit),nullable,signed...
	len int unsigned,
	prec int unsigned,
	primary key(table_id,pos)
) engine=innodb row_format=redundant;

-- index_id 3
drop table if exists sys_indexes;
create table sys_indexes(
	table_id bigint unsigned,
	id bigint unsigned, -- indexid
	name varchar(255),
	n_fields int unsigned,
	`type` int unsigned,
	space int unsigned,
	page_no int unsigned,
	merge_threshold int unsigned,
	primary key(table_id,id)
) engine=innodb row_format=redundant;

-- index_id 4
drop table if exists sys_fields;
create table sys_fields(
	index_id bigint unsigned,
	pos int unsigned,
	col_name varchar(255),
	primary key(index_id,pos)
) engine=innodb row_format=redundant;

-- index_id 11
drop table if exists sys_foreign;
create table sys_foreign(
	name varchar(255),
	for_name varchar(255),
	ref_name varchar(255),
	n_cols int unsigned,
	primary key(name), -- index_id 11
	key(for_name), -- index_id 12
	key(ref_name) -- index_id 13
) engine=innodb row_format=redundant;

-- index_id 14 
drop table if exists sys_foreign_cols;
create table sys_foreign_cols(
	name varchar(255),
	pos int unsigned,
	for_col_name varchar(255),
	ref_col_name varchar(255),
	primary key(name, pos)
) engine=innodb row_format=redundant;

-- index_id 15 
drop table if exists sys_tablespaces;
create table sys_tablespaces(
	space int unsigned, -- index_id
	name varchar(255),
	flags int unsigned,
	primary key(space)
) engine=innodb row_format=redundant;

-- index_id 16 
drop table if exists sys_datafiles;
create table sys_datafiles(
	space int unsigned, -- index_id
	path varchar(255),
	primary key(space)
) engine=innodb row_format=redundant;

-- index_id 17 
drop table if exists sys_virtual;
create table sys_virtual(
	table_id bigint unsigned,
	pos int unsigned,
	base_pos int unsigned,
	primary key(table_id,pos,base_pos)
) engine=innodb row_format=redundant;

/*
DICT_TF_WIDTH_ZIP_SSIZE = 4
DICT_TF_POS_COMPACT = 0
DICT_TF_WIDTH_COMPACT = 1
DICT_TF_POS_ZIP_SSIZE = (DICT_TF_POS_COMPACT + DICT_TF_WIDTH_COMPACT)
DICT_TF_MASK_ZIP_SSIZE = ((~(~0 << DICT_TF_WIDTH_ZIP_SSIZE)) << DICT_TF_POS_ZIP_SSIZE)

DICT_TF_WIDTH_ATOMIC_BLOBS = 1
DICT_TF_POS_ATOMIC_BLOBS = (DICT_TF_POS_ZIP_SSIZE + DICT_TF_WIDTH_ZIP_SSIZE)
DICT_TF_MASK_ATOMIC_BLOBS = ((~(~0 << DICT_TF_WIDTH_ATOMIC_BLOBS)) << DICT_TF_POS_ATOMIC_BLOBS)

DICT_TF_WIDTH_DATA_DIR = 1
DICT_TF_POS_DATA_DIR = (DICT_TF_POS_ATOMIC_BLOBS + DICT_TF_WIDTH_ATOMIC_BLOBS)
DICT_TF_MASK_DATA_DIR = ((~(~0 << DICT_TF_WIDTH_DATA_DIR)) << DICT_TF_POS_DATA_DIR)

DICT_TF_WIDTH_SHARED_SPACE = 1
DICT_TF_POS_SHARED_SPACE = (DICT_TF_POS_DATA_DIR + DICT_TF_WIDTH_DATA_DIR)
DICT_TF_MASK_SHARED_SPACE = ((~(~0 << DICT_TF_WIDTH_SHARED_SPACE)) << DICT_TF_POS_SHARED_SPACE)
*/

DICT_TF_MASK_ZIP_SSIZE = 30;
DICT_TF_MASK_ATOMIC_BLOBS = 32;
DICT_TF_MASK_DATA_DIR = 64;
DICT_TF_MASK_SHARED_SPACE = 128;

type = 1;
type |= flags & (DICT_TF_MASK_ZIP_SSIZE
			 | DICT_TF_MASK_ATOMIC_BLOBS
			 | DICT_TF_MASK_DATA_DIR
			 | DICT_TF_MASK_SHARED_SPACE);

def GET_ROWFORMT_FROM_TABLE_FLAGS(flags):
	if not flags&1: # 第1bit固定是1, 所以这个永远用不到.
		return 'REDUNDANT'
	elif not (flags&32)>>5:
		return 'COMPACT'
	elif (flags&30)>>1:
		return 'COMPRESSED'
	else:
		return 'DYNAMIC'

/* 创建虚拟列 */
dict_create_v_col_pos(
	ulint	v_pos,
	ulint	col_pos)
{
	ut_ad(v_pos <= REC_MAX_N_FIELDS);
	ut_ad(col_pos <= REC_MAX_N_FIELDS);

	return(((v_pos + 1) << 16) + col_pos);
}

/*获取虚拟列的虚拟pos, 也就是第几个虚拟列*/
dict_get_v_col_pos(
	ulint	pos)
{
	return((pos >> 16) - 1);
}

/*获取虚拟列的真实pos, 其它字段都pos之后,再把虚拟列插进去*/
ulint
dict_get_v_col_mysql_pos(
	ulint	pos)
{
	return(pos & 0xFFFF);
}

`gen_virtual` int(11) GENERATED ALWAYS AS ((`id_default` + 1)) VIRTUAL, -- prec=1
`gen_stored` int(11) GENERATED ALWAYS AS ((`int_col` + 1)) STORED, -- prec=0 stored是存储在磁盘上的
`concat_char` varchar(201) GENERATED ALWAYS AS (concat(convert(`char_col` using utf8mb4),' ',`varchar_col`)) VIRTUAL, -- prec=2

(root@127.0.0.1) [(none)]> create table db1.t20251231_01(id int,name varchar(200)) comment='MERGE_THRESHOLD=40';
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [(none)]> show create table db1.t20251231_01\G
*************************** 1. row ***************************
       Table: t20251231_01
Create Table: CREATE TABLE `t20251231_01` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='MERGE_THRESHOLD=40'
1 row in set (0.00 sec)


[root@ddcw21 ibd2sql-ibd2sql-v2.x]#python3 main.py /data/mysql_5744/mysqldata/ibdata1 --scan --table=sys_tables| grep t20251231_01
PAGENO: 395 INSERT INTO `db1`.`sys_tables` VALUES ('db1/t20251231_01',20145,2,33,0,80,null,20163)

[root@ddcw21 ibd2sql-ibd2sql-v2.x]#python3 main.py /data/mysql_5744/mysqldata/ibdata1 --scan --table=sys_indexes | grep 20145
PAGENO: 1034 INSERT INTO `db1`.`sys_indexes` VALUES (20145,20196,'GEN_CLUST_INDEX',0,1,20163,4294967295,40)

CREATE TABLE t1 (
   id INT,
  KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'
);