ibd2sql解析表结构DDL问题

nicholascp commented 9 months ago

解析表结果时，如果列类型 timestamp且有默认值是，建表语句 xx timestamp default 'current_timestamp' ,不需要''

如果blob/text字段，在key里面，会丢失，长度 key(xx,text) ---> key(xx,text(11))

ddcw commented 9 months ago

解析表结果时，如果列类型 timestamp且有默认值是，建表语句 xx timestamp default 'current_timestamp' ,不需要''

如果blob/text字段，在key里面，会丢失，长度 key(xx,text) ---> key(xx,text(11))

感谢您提供下的BUG.

原先未考虑默认值为函数的情况, 现在支持了. 取值为 sdi default_option
原先不支持非varchar的前缀索引, 现在支持了, 并区分了utf8mb4 你可以下载新最新版的ibd2sql (源码) 去测试一下

如下为修复后的测试例子:

SHELL> python3 main.py /data/mysql_3314/mysqldata/ibd2sql/t20240126.ibd 
CREATE TABLE IF NOT EXISTS `ibd2sql`.`t20240126`(
    `id` int NOT NULL,
    `aa` varchar(200) NULL,
    `bb` blob NULL,
    `cc` timestamp DEFAULT (now()),
    `dd` datetime DEFAULT (now()),
    `ee` int NULL,
    PRIMARY KEY  (`id`),
    KEY `aa` (`aa`,`bb`(10)),
    KEY `bb` (`bb`(10),`aa`(10)),
    KEY `bb_2` (`bb`(20),`ee`),
    KEY `bb_3` (`bb`(30),`aa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

nicholascp commented 9 months ago

dd datetime DEFAULT (now()) ---》建表的时候，函数也不需要（），索引那块tinytext好像还是有问题

root@mysql 09:25:  [cpbak]> create table test(id timestamp default now());
Query OK, 0 rows affected (0.01 sec)

root@mysql 09:25:  [cpbak]> create table test1(id timestamp default (now()));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(now()))' at line 1

[root@vm1 py]# python3 main.py /opt/data/mysql8/orch/database_instance_topology_history.ibd
CREATE TABLE IF NOT EXISTS `orch`.`database_instance_topology_history`(
    `snapshot_unix_timestamp` int unsigned NOT NULL,
    `hostname` varchar(128) NOT NULL,
    `port` smallint unsigned NOT NULL,
    `master_host` varchar(128) NOT NULL,
    `master_port` smallint unsigned NOT NULL,
    `cluster_name` tinytext NOT NULL,
    `version` varchar(128) NOT NULL,
    PRIMARY KEY  (`snapshot_unix_timestamp`,`hostname`,`port`),
    KEY `cluster_name_idx_database_instance_topology_history` (`snapshot_unix_timestamp`,`cluster_name`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_general_ci ;

tinytext好像还是没有带上长度

ddcw / ibd2sql

ibd2sql解析表结构DDL问题 #8