[MYSQL] 提取超复杂表的DDL

原创

大大刺猬

发布于 2024-12-11 15:44:27

4070

文章被收录于专栏：大大刺猬大大刺猬

导读

在学习mysql的表结构的时候, 会接触到各种数据类型, 各种索引, 那么把它们都柔和到一起会怎么样呢?

分区表和空间坐标

有空间坐标字段的时候是不支持分区表的, 会报错ERROR 1178 (42000): The storage engine for the table doesn't support GEOMETRY, 这个报错其实不是说存储引擎不支持GEOMETRY, 而且在某些条件下(比如含有分区). 我们来看个例子:

只有分区表

create table t20241211_p(
  `id` int DEFAULT NULL,
  `date_col` date DEFAULT NULL,
  `datetime_col` datetime(6)
)engine=innodb PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB);

我们看到是没问题的,(肯定支持分区表啊, 但出于严谨, 我们还是测试一下)

只有空间坐标

create table t20241211_geometry(
  `id` int DEFAULT NULL,
  `date_col` date DEFAULT NULL,
  `datetime_col` datetime(6),
  `spatial_geometry` geometry
)engine=innodb ;

不用想也知道肯定是支持的, 但还是测试一下. 看起来也是没问题的

分区表加上空间坐标

既然分开没问题, 那我们合起来看下呢

create table t20241211_p_and_g(
  `id` int DEFAULT NULL,
  `date_col` date DEFAULT NULL,
  `datetime_col` datetime(6),
  `spatial_geometry` geometry
)engine=innodb PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB);

吼吼, 报错了, 说不支持空间坐标.(咋不说不支持分区表呢...)

测试了5.7 --> 8.0.39 都是不支持的.

那我们看看官网对此如何解释的呢. 我没找到, 但有大佬找到了.

Spatial columns. Columns with spatial data types such as POINT or GEOMETRY cannot be used in partitioned tables.

某些国产数据库的文档, 只有最内测才是内容, 也就是标题这个链接是点不开的(说的就是ob). 但oracle这种都是能点击的, 如果不点击标题就会错过很多信息, 比如这次这个我就直接点击的最内层,导致错过了这个信息.... 就挺离谱的...

复杂表的DDL提取

所以我们的复杂DDL是不包含分区的..., 由于测试版本是8.0.28和5.7.38 所以也不支持向量类型..., 前缀索引也忘了...

好了, 来看看这个超复杂的DDL吧.

create table test_ibd2sql_ddl_00(
	id bigint unsigned not null primary key auto_increment,
  	name varchar(200)
);

create table test_ibd2sql_ddl_01(
  `id` serial primary key auto_increment, -- serial: bigint unsigned not null
  `id_default` int default 0,
  `id_unsigned_zerofill` int unsigned zerofill,
  `int_col` int DEFAULT NULL,
  `id_invisible` int /*!80023 INVISIBLE */,
  `tinyint_col` tinyint DEFAULT '1',
  `boolean_col` boolean, -- tinyint(1)
  `smallint_col` smallint DEFAULT NULL,
  `mediumint_col` mediumint DEFAULT NULL,
  `bigint_col` bigint DEFAULT NULL,
  `float_col` float DEFAULT NULL,
  `double_col` double DEFAULT NULL,
  `decimal_col` decimal(10,2) DEFAULT NULL,
  `date_col` date DEFAULT NULL,
  `datetime_col` datetime(6),
  `timestamp_col` timestamp DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `time_col` time(4) DEFAULT NULL,
  `year_col` year DEFAULT NULL,
  `char_col` char(100) CHARACTER SET utf8 COLLATE utf8_danish_ci DEFAULT NULL,
  `nchar_col` nchar(10), -- 同char(10)
  `varchar_col` varchar(100),
  `nvarchar_col` nvarchar(10), -- 同nvarchar(10)
  `binary_col` binary(10) DEFAULT NULL,
  `varbinary_col` varbinary(20) DEFAULT NULL,
  `bit_col` bit(4) DEFAULT NULL,
  `enum_col` enum('A','B','C'),
  `set_col` set('X','Y','Z'),
  `json_type_col` json DEFAULT NULL,
  `tinyblob_col` tinyblob,
  `mediumblob_col` mediumblob,
  `blob_col` blob,
  `longblob_col` longblob,
  `tinytext_col` tinytext,
  `mediumtext_col` mediumtext,
  `text_col` text,
  `longtext_col` longtext,
  `gen_stored` INT GENERATED ALWAYS AS (int_col + 1) STORED,
  `gen_virtual` INT GENERATED ALWAYS AS (id_default + 1) virtual,
  `spatial_geometry` geometry,
  `spatial_point` point not null /*!80003 SRID 4326 */,
  `spatial_linestring` linestring,
  `spatial_polygon` polygon,
  `spatial_geometrycollection` geometrycollection,
  `spatial_multipoint` multipoint,
  `spatial_multilinestring` multilinestring,
  `spatial_multipolygon` multipolygon,
  `concat_char` varchar(201) as (concat(char_col,' ',varchar_col)),
  unique key(int_col),
  key(bigint_col),
  key(concat_char),
  key(varchar_col desc),
  key(int_col,time_col),
  key(int_col) /*!80000 INVISIBLE */,
  fulltext(varchar_col,text_col),
  spatial index(spatial_point),
  check (int_col>0 and tinyint_col>0),
  foreign key(id) references test_ibd2sql_ddl_00(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

基本上囊括了Mysql的所有字段和所有索引. 用来做测试是相当不错的. 尤其是某些情况只剩数据文件的时候, 就非常需要数据文件对应的DDL了.

该DDL比较复杂, 但不难, 我就不解释了.

在5.7环境提取DDL

在mysql 5.7环境下, 表的元数据信息是放在.frm文件中的, 只有解析该文件就可以得到对应DDL, 我目前用过的工具有两款: mysql官方的mysqlfrm和第三方的dbsake. 安装下载这里就不演示了. 我们直接开始使用吧.

mysqlfrm

先来看看官方的mysqlfrm, 我之前吐槽过它丢失时间字段(date,datetime,timestamp)精度. 这次再来看看还有没得其它问题吧.

mysqlfrm --diagnostic /data/mysql_3308/mysqldata/db1/test_ibd2sql_ddl_01.frm

结果如下: 太长了,截图不方便, 我就直接复制了

3:44:18 [root@ddcw21 ~]#mysqlfrm --diagnostic /data/mysql_3308/mysqldata/db1/test_ibd2sql_ddl_01.frm 
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /data/mysql_3308/mysqldata/db1/test_ibd2sql_ddl_01.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `db1`.`test_ibd2sql_ddl_01` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
  `id_default` int(11) DEFAULT NULL, 
  `id_unsigned_zerofill` int(10) unsigned zerofill DEFAULT NULL, 
  `int_col` int(11) DEFAULT NULL, 
  `id_invisible` int(11) DEFAULT NULL, 
  `tinyint_col` tinyint(4) DEFAULT NULL, 
  `boolean_col` tinyint(1) DEFAULT NULL, 
  `smallint_col` smallint(6) DEFAULT NULL, 
  `mediumint_col` mediumint(9) DEFAULT NULL, 
  `bigint_col` bigint(20) DEFAULT NULL, 
  `float_col` float DEFAULT NULL, 
  `double_col` double DEFAULT NULL, 
  `decimal_col` decimal(10,2) DEFAULT NULL, 
  `date_col` date DEFAULT NULL, 
  `datetime_col` datetime DEFAULT NULL, 
  `timestamp_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `time_col` time DEFAULT NULL, 
  `year_col` year(4) DEFAULT NULL, 
  `char_col` char(300) CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `nchar_col` char(30) CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `varchar_col` varchar(400) DEFAULT NULL, 
  `nvarchar_col` varchar(30) CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `binary_col` char(10) CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `varbinary_col` varchar(20) CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `bit_col` bit(4) DEFAULT NULL, 
  `enum_col` enum('A','B','C') DEFAULT NULL, 
  `set_col` set('X','Y','Z') DEFAULT NULL, 
  `json_type_col` decimal(6,16) DEFAULT NULL, 
  `tinyblob_col` tinyblob CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `mediumblob_col` mediumblob CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `blob_col` blob CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `longblob_col` longblob CHARACTER SET <UNKNOWN> DEFAULT NULL, 
  `tinytext_col` tinytext DEFAULT NULL, 
  `mediumtext_col` mediumtext DEFAULT NULL, 
  `text_col` text DEFAULT NULL, 
  `longtext_col` longtext DEFAULT NULL, 
  `gen_stored` int(11) DEFAULT NULL, 
  `gen_virtual` int(11) DEFAULT NULL, 
  `spatial_geometry` text DEFAULT NULL, 
  `spatial_point` text, 
  `spatial_linestring` text DEFAULT NULL, 
  `spatial_polygon` text DEFAULT NULL, 
  `spatial_geometrycollection` text DEFAULT NULL, 
  `spatial_multipoint` text DEFAULT NULL, 
  `spatial_multilinestring` text DEFAULT NULL, 
  `spatial_multipolygon` text DEFAULT NULL, 
  `concat_char` varchar(804) DEFAULT NULL, 
PRIMARY KEY `PRIMARY` (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `int_col` (`int_col`),
KEY `bigint_col` (`bigint_col`),
KEY `varchar_col` (`varchar_col`),
KEY `int_col_2` (`int_col`,`time_col`),
KEY `int_col_3` (`int_col`),
KEY `spatial_point` (`spatial_point`),
KEY `concat_char` (`concat_char`),
KEY `varchar_col_2` (`varchar_col`,`text_col`) USING FULLTEXT
) ENGINE=InnoDB;

#...done.

unknown部分是字符集相关的, 不登录数据库就不知道排序规则id对应的字符集信息, 这不能怪这个工具. 但我们其实可以提前将对应信息查询出来的, 保存为字典就行, 比如set session group_concat_max_len=10240000; SELECT CONCAT('{', GROUP_CONCAT(CONCAT('"', b.id, '":{"COLLATION_NAME":"', b.COLLATION_NAME, '","CHARACTER_SET_NAME":"', b.CHARACTER_SET_NAME, '","MAXLEN":"', a.MAXLEN, '","DEFAULT_COLLATE_NAME":"', a.DEFAULT_COLLATE_NAME, '","DESCRIPTION":"', a.DESCRIPTION, '"}')), '}') AS pydict_charset FROM information_schema.CHARACTER_SETS AS a JOIN information_schema.COLLATIONS AS b ON a.CHARACTER_SET_NAME = b.CHARACTER_SET_NAME;
id_default 的默认值0 丢失了.....
datetime的精度丢了, 之前也提到过
生成列(stored和virtual)都错了, 被当作了普通字段...
空间索引(SPATIAL)被当作普通索引了
全文索引(FULLTEXT)写法变了.....
外键直接没了.
check也没了,不过5.7版本show create table也看不到,就不怪它了.(可在information_schema.TABLE_CONSTRAINTS里面查看)

mysql官方的mysqlfrm看起来问题还是不少的.... (怀疑是偷懒)

dbsake

现在来看看第三方工具dbsake吧, 看起来有10+年历史了...

./dbsake  frmdump /data/mysql_3308/mysqldata/db1/test_ibd2sql_ddl_01.frm

直接报错了....

看起来是枚举类型某个地方decode的时候的问题, 应该就是解析frm的时候没有考虑周全. 也不知道是哪个字段影响的, 懒得去试了. (之前测试过很多类型都是能够解析的, 至少datetime之类的精度是保留的. 但遇到不支持的情况就直接报错了, 这点可能不如官方的mysqlfrm)

我也顺便测试了下我以前解析的frm的工具, 发现也不行, 只解析到char_col就gg了

整体看来复制的表在5.7里面均不太好解析.

在8.0环境提取DDL

那我们来看看8.0的表现如何. 在mysql8.0环境, 元数据信息是放在ibd里面的sdi page的. 所以只有解析出sdi信息就能自己拼接SQL语句了. 而mysql官方有个ibd2sdi的工具就能提取出sdi信息(不支持general tablespace也是离谱,也怀疑是官方偷懒), 当然我们之前也写过很多关于sdi的文章的, 有兴趣的自己往前面翻一翻.

本次重点是提取DDL, 这种工具市场上其实不多, 而开源的就更少了, 主要是拼接DDL太麻烦, 又用处不大, 有那时间不如***.

ibd2sdi + sdi2ddl

首先出场的是官方的ibd2sdi加上第三方的sdi2ddl, 该工具比较年轻, 才1年. 看起来是国外大佬使用c++写的. 虽然作者提供了二进制版本, 但是要求GLIBC_2.34也是比较难受的. 自己编译的话, 依赖项又太多..... ubuntu 22的glibc版本就是2.35 够用.

那就开始吧.

ibd2sdi test_ibd2sql_ddl_01.ibd | ./sdi2ddl

啊这, 不支持index为5的类型.... 查看我们以前的笔记, 发现index_type=5的是空间坐标.

这就尴尬了啊, 不行, 我先去给作者提个issue了再来继续写.(看issue貌似还不支持fulltext...)

出师未捷身先死....

pyinnodb

卧槽, 发现个NB的项目: pyinnodb, 看起来是使用python写的能解析ibd文件的, 而且还能直接读取frm文件. 试试效果如何呢

./pyinnodb.sh test_ibd2sql_ddl_01.ibd tosql --mode ddl

结果如下

ubuntu@VM-32-48-ubuntu:~$ ./pyinnodb.sh test_ibd2sql_ddl_01.ibd tosql --mode ddl
CREATE TABLE `db1`.`test_ibd2sql_ddl_01` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `id_default` int DEFAULT '0',
    `id_unsigned_zerofill` int(10) unsigned zerofill DEFAULT NULL,
    `int_col` int DEFAULT NULL,
    `id_invisible` int DEFAULT NULL,
    `tinyint_col` tinyint DEFAULT '1',
    `boolean_col` tinyint(1) DEFAULT NULL,
    `smallint_col` smallint DEFAULT NULL,
    `mediumint_col` mediumint DEFAULT NULL,
    `bigint_col` bigint DEFAULT NULL,
    `float_col` float DEFAULT NULL,
    `double_col` double DEFAULT NULL,
    `decimal_col` decimal(10,2) DEFAULT NULL,
    `date_col` date DEFAULT NULL,
    `datetime_col` datetime(6) DEFAULT NULL,
    `timestamp_col` timestamp DEFAULT (CURRENT_TIMESTAMP) ON UPDATE CURRENT_TIMESTAMP,
    `time_col` time(4) DEFAULT NULL,
    `year_col` year DEFAULT NULL,
    `char_col` char(100) DEFAULT NULL,
    `nchar_col` char(10) DEFAULT NULL,
    `varchar_col` varchar(100) DEFAULT NULL,
    `nvarchar_col` varchar(10) DEFAULT NULL,
    `binary_col` binary(10) DEFAULT NULL,
    `varbinary_col` varbinary(20) DEFAULT NULL,
    `bit_col` bit(4) DEFAULT NULL,
    `enum_col` enum('A','B','C') DEFAULT NULL,
    `set_col` set('X','Y','Z') DEFAULT NULL,
    `json_type_col` json DEFAULT NULL,
    `tinyblob_col` tinyblob DEFAULT NULL,
    `mediumblob_col` mediumblob DEFAULT NULL,
    `blob_col` blob DEFAULT NULL,
    `longblob_col` longblob DEFAULT NULL,
    `tinytext_col` tinytext DEFAULT NULL,
    `mediumtext_col` mediumtext DEFAULT NULL,
    `text_col` text DEFAULT NULL,
    `longtext_col` longtext DEFAULT NULL,
    `gen_stored` int GENERATED ALWAYS AS ((`int_col` + 1)) STORED,
    `gen_virtual` int GENERATED ALWAYS AS ((`id_default` + 1)) VIRTUAL,
    `spatial_geometry` geometry DEFAULT NULL,
    `spatial_point` point NOT NULL,
    `spatial_linestring` linestring DEFAULT NULL,
    `spatial_polygon` polygon DEFAULT NULL,
    `spatial_geometrycollection` geomcollection DEFAULT NULL,
    `spatial_multipoint` multipoint DEFAULT NULL,
    `spatial_multilinestring` multilinestring DEFAULT NULL,
    `spatial_multipolygon` multipolygon DEFAULT NULL,
    `concat_char` varchar(201) GENERATED ALWAYS AS (concat(`char_col`,_utf8mb4\' \',`varchar_col`)) VIRTUAL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id` (`id`),
    UNIQUE KEY `int_col` (`int_col`),
    KEY `bigint_col` (`bigint_col`),
    KEY `varchar_col` (`varchar_col`),
    KEY `int_col_2` (`int_col`,`time_col`),
    KEY `int_col_3` (`int_col`),
    SPATIAL KEY `spatial_point` (`spatial_point`),
    KEY `concat_char` (`concat_char`),
    FULLTEXT KEY `varchar_col_2` (`varchar_col`,`text_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
ubuntu@VM-32-48-ubuntu:~$

id_invisible的invisible属性没有列出来
spatial_point的srid没有列出来
没有外键
没有约束

目前只发现这4个小问题, 整体来说已经很强了, 起码比官方的强.

ibd2sql

接下来上场的是ibd2sql. (和官方的ibd2sdi名字比较像, 不要搞混了). 该工具也是使用python写的, 直接来看看效果吧.

python3 main.py /data/mysql_3314/mysqldata/db1/test_ibd2sql_ddl_01.ibd --ddl

效果如下:

15:21:49 [root@ddcw21 ibd2sql]#python3 main.py /data/mysql_3314/mysqldata/db1/test_ibd2sql_ddl_01.ibd --ddl
CREATE TABLE IF NOT EXISTS `db1`.`test_ibd2sql_ddl_01`(
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `id_default` int NULL DEFAULT '0',
    `id_unsigned_zerofill` int(10) unsigned zerofill NULL,
    `int_col` int NULL,
    `id_invisible` int NULL,
    `tinyint_col` tinyint NULL DEFAULT '1',
    `boolean_col` tinyint(1) NULL,
    `smallint_col` smallint NULL,
    `mediumint_col` mediumint NULL,
    `bigint_col` bigint NULL,
    `float_col` float NULL,
    `double_col` double NULL,
    `decimal_col` decimal(10,2) NULL,
    `date_col` date NULL,
    `datetime_col` datetime(6) NULL,
    `timestamp_col` timestamp DEFAULT (CURRENT_TIMESTAMP) ON UPDATE CURRENT_TIMESTAMP,
    `time_col` time(4) NULL,
    `year_col` year NULL,
    `char_col` char(100) NULL,
    `nchar_col` char(10) NULL,
    `varchar_col` varchar(100) NULL,
    `nvarchar_col` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
    `binary_col` binary(10) NULL,
    `varbinary_col` varbinary(20) NULL,
    `bit_col` bit(4) NULL,
    `enum_col` enum('A','B','C') NULL,
    `set_col` set('X','Y','Z') NULL,
    `json_type_col` json NULL,
    `tinyblob_col` tinyblob NULL,
    `mediumblob_col` mediumblob NULL,
    `blob_col` blob NULL,
    `longblob_col` longblob NULL,
    `tinytext_col` tinytext NULL,
    `mediumtext_col` mediumtext NULL,
    `text_col` text NULL,
    `longtext_col` longtext NULL,
    `gen_stored` int NULL,
    `gen_virtual` int GENERATED ALWAYS AS ((`id_default` + 1)) VIRTUAL,
    `spatial_geometry` geometry NULL,
    `spatial_point` point /*!80003 SRID 4326 */ NOT NULL,
    `spatial_linestring` linestring NULL,
    `spatial_polygon` polygon NULL,
    `spatial_geometrycollection` geomcollection NULL,
    `spatial_multipoint` multipoint NULL,
    `spatial_multilinestring` multilinestring NULL,
    `spatial_multipolygon` multipolygon NULL,
    `concat_char` varchar(201) GENERATED ALWAYS AS (concat(`char_col`,_utf8mb4' ',`varchar_col`)) VIRTUAL,
    PRIMARY KEY  (`id` ),
    UNIQUE KEY `id` (`id` ),
    UNIQUE KEY `int_col` (`int_col` ),
    KEY `bigint_col` (`bigint_col` ),
    KEY `varchar_col` (`varchar_col` DESC),
    KEY `int_col_2` (`int_col` ,`time_col` ),
    KEY `int_col_3` (`int_col` ) /*!80000 INVISIBLE */,
    SPATIAL KEY `spatial_point` (`spatial_point` ),
    KEY `concat_char` (`concat_char` ),
    FULLTEXT KEY `varchar_col_2` (`varchar_col` ,`text_col` ),
    CONSTRAINT `test_ibd2sql_ddl_01_ibfk_1` FOREIGN KEY (`id`) REFERENCES `db1`.`test_ibd2sql_ddl_00` (`id`),
    CONSTRAINT `test_ibd2sql_ddl_01_chk_1` CHECK ((`int_col` > 0) and (`tinyint_col` > 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

暂未发现问题, 自己写的, 不容易发现问题....

其实有个on update忘了写, 写本文的时候顺便补上了 -_-

总结

太复杂的表(含不常用的功能) 很难解析. 建议不要整那么复杂的.

目前发现 5.7的话, 都不咋地; 8.0的话, ibd2sql略胜一筹

参考: https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

ddl

mysql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

ddl

mysql

登录后参与评论

0 条评论

热度