1. 为什么需要把 MySQL 表结构搬到 ClickHouse做过数据平台迁移的人大概率都遇到过这个场景业务系统跑在 MySQL 上数据量一天天涨单表几千万行之后查询开始变慢分析类需求越来越扛不住于是决定把分析链路切到 ClickHouse。这时候第一个拦路虎不是数据同步而是表结构怎么迁。MySQL 和 ClickHouse 虽然都支持 SQL但两者的类型系统、建表语法、索引机制完全是两套逻辑。MySQL 的INT UNSIGNED、TINYINT(1)、DATETIME(3)、VARCHAR(255)、TEXT、DECIMAL(10,2)这些类型在 ClickHouse 里没有一一对应的写法。更麻烦的是MySQL 建表语句里那一堆ENGINEInnoDB、CHARSETutf8mb4、COLLATE、AUTO_INCREMENT、COMMENT、PRIMARY KEY、KEY idx_xxxClickHouse 要么不认要么语义完全不同。手工改一张几十个字段的表改起来半小时起步还容易漏字段、写错类型。几十上百张表呢纯手工就是灾难。所以自动化转换这件事本质上解决的是迁移效率和一致性两个问题既要快又要保证字段名、类型映射、注释这些信息不丢不错。我这次要分享的方案是用自定义 UDF用户自定义函数来做这件事。注意这里的 UDF 不是指 ClickHouse 原生的 SQL UDF而是指在数据集成/ETL 工具里通过编写自定义函数逻辑把 MySQL 的information_schema元数据读出来经过类型映射和语法重组直接生成 ClickHouse 的CREATE TABLE语句。这套思路的好处是一次写好所有表通用而且映射规则完全掌握在自己手里遇到特殊类型随时改。适合谁来参考如果你正在做 MySQL 到 ClickHouse 的迁移、数仓分层建设、或者需要批量生成建表 DDL这篇内容应该能帮你省下大量重复劳动。下面我会从整体思路、类型映射规则、UDF 实现细节、踩坑经验几个维度展开尽量把每一步的为什么讲清楚。2. 整体转换思路从元数据到 DDL 的完整链路2.1 转换的核心逻辑拆解把 MySQL 表结构转成 ClickHouse 表结构说白了就是一次结构翻译。翻译的输入是 MySQL 的元数据输出是 ClickHouse 的建表语句。中间要经过三个关键环节第一个环节是元数据采集。MySQL 把表结构信息存在information_schema库里主要用到两张表COLUMNS存字段信息字段名、类型、是否可空、默认值、注释、字符集等TABLES存表级信息引擎、注释、字符集等。通过查询这两张表就能拿到一张表的完整结构描述。第二个环节是类型映射。这是整个转换里最需要经验的部分。MySQL 的类型和 ClickHouse 的类型不是简单的一对一有些是一对多有些需要根据精度参数决定。比如INT在 ClickHouse 里可以映射成Int32但如果 MySQL 里是INT UNSIGNED就得映射成UInt32DATETIME通常映射成DateTime但如果带了毫秒精度DATETIME(3)就得用DateTime64(3)。第三个环节是DDL 重组。把映射后的字段名、类型、注释拼成 ClickHouse 的建表语法。这里要注意 ClickHouse 的建表语句结构和 MySQL 差别很大ClickHouse 用ENGINE MergeTree()指定引擎用ORDER BY指定排序键相当于主键的角色用PARTITION BY指定分区字段注释用COMMENT xxx跟在字段后面。2.2 为什么选择 UDF 而不是写脚本有人可能会问直接写个 Python 脚本读元数据、拼 SQL 不就行了为什么要用 UDF这里要区分场景。如果你是一次性迁移写个脚本确实够了。但如果是持续性的、多批次的迁移比如数据平台要不断接入新的业务库每次都要转换表结构那 UDF 的优势就体现出来了它可以嵌入到数据集成流程里作为一个标准算子被反复调用不需要每次单独跑脚本。而且 UDF 的输入输出是标准化的输入表名和库名输出建表语句调用方不用关心内部实现。另一个原因是可维护性。类型映射规则会随着业务变化调整比如某天发现TEXT类型映射得不对需要改。如果映射逻辑封装在 UDF 里改一处就全局生效如果散落在多个脚本里改起来就容易漏。提示如果你的迁移是一次性的、表数量不多写脚本完全够用。UDF 更适合需要反复执行、集成到平台流程里的场景。不要为了用 UDF 而用 UDF。2.3 转换流程的完整链路把整个链路串起来大概是这样的输入MySQL 库名 表名或者批量输入一批表名查询information_schema.COLUMNS获取字段列表按ORDINAL_POSITION排序查询information_schema.TABLES获取表级信息注释、引擎等遍历每个字段根据DATA_TYPE和COLUMN_TYPE做类型映射处理可空性MySQL 的IS_NULLABLE为YES时ClickHouse 字段用Nullable(类型)处理默认值和注释拼接成 ClickHouse 的CREATE TABLE语句输出 DDL这个链路里第 4 步是核心也是最容易出问题的地方。下面单独展开讲。3. MySQL 到 ClickHouse 的类型映射规则详解3.1 数值类型的映射与坑点数值类型是映射里相对简单的一类但有几个细节不注意就会踩坑。先看整数类型。MySQL 的TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别对应 ClickHouse 的Int8、Int16、Int32、Int32、Int64。注意MEDIUMINT在 ClickHouse 里没有直接对应通常映射成Int32。如果 MySQL 里带了UNSIGNED就要换成对应的无符号类型UInt8、UInt16、UInt32、UInt64。这里有个特别容易忽略的点TINYINT(1)在 MySQL 里通常被当作布尔值用。很多 ORM 框架会把布尔字段建成TINYINT(1)。如果直接映射成Int8虽然能用但语义上不够清晰。我的做法是判断COLUMN_TYPE是否等于tinyint(1)如果是映射成UInt8并保留注释说明或者根据业务约定映射成BoolClickHouse 支持Bool类型。这个规则要根据实际业务来定没有绝对标准。浮点类型方面FLOAT映射成Float32DOUBLE映射成Float64。DECIMAL(M,D)映射成Decimal(M,D)但要注意 ClickHouse 的Decimal精度上限是 76 位MySQL 的DECIMAL最大是 65 位一般不会超。不过 ClickHouse 的Decimal在运算时性能不如整数如果业务允许可以考虑用整数存储金额比如以分为单位但这属于业务层面的优化不在自动转换的范畴。MySQL 类型ClickHouse 类型说明TINYINTInt8带 UNSIGNED 则 UInt8TINYINT(1)UInt8 / Bool布尔语义按业务约定SMALLINTInt16带 UNSIGNED 则 UInt16MEDIUMINTInt32ClickHouse 无直接对应INTInt32带 UNSIGNED 则 UInt32BIGINTInt64带 UNSIGNED 则 UInt64FLOATFloat32精度可能损失DOUBLEFloat64基本无损DECIMAL(M,D)Decimal(M,D)精度上限 763.2 字符串与时间类型的映射字符串类型是映射里变化最多的。MySQL 的CHAR(N)和VARCHAR(N)在 ClickHouse 里统一用String因为 ClickHouse 的String是变长的不区分定长和变长。TEXT、MEDIUMTEXT、LONGTEXT也统一映射成String。BLOB系列通常映射成String或者String的二进制变体具体看业务是否需要保留二进制语义。这里有个经验MySQL 的VARCHAR(N)里的 N 在 ClickHouse 里会丢失。ClickHouse 的String不限制长度所以转换后长度约束就没了。如果业务上依赖长度约束做校验需要在应用层补回来或者用FixedString(N)但FixedString要求长度严格一致用起来限制多一般不推荐。时间类型方面DATE映射成DateDATETIME映射成DateTimeTIMESTAMP也映射成DateTime。但要注意时区问题MySQL 的TIMESTAMP是带时区的DATETIME不带。ClickHouse 的DateTime默认用服务器时区如果需要指定时区可以写成DateTime(Asia/Shanghai)。这个在跨时区业务里很关键转换时要根据业务约定决定是否显式指定时区。如果 MySQL 里是DATETIME(3)或DATETIME(6)带毫秒或微秒精度就要映射成DateTime64(3)或DateTime64(6)。这个精度参数从DATETIME_PRECISION字段里取。漏掉这个精度数据写入时毫秒部分会被截断对某些业务是致命的。3.3 特殊类型与默认值的处理除了常规类型还有几类特殊情况需要单独处理。ENUM类型在 MySQL 里很常见ClickHouse 也支持Enum但语法不同。MySQL 是ENUM(a,b,c)ClickHouse 是Enum8(a 1, b 2, c 3)需要给每个值分配一个整数编号。转换时要解析COLUMN_TYPE里的枚举值列表然后生成对应的Enum8或Enum16值数量超过 127 用Enum16。不过实际经验是很多团队迁移时直接把ENUM映射成String简单省事代价是失去枚举约束。这个取舍看业务需求。SET类型在 ClickHouse 里没有直接对应通常映射成String或者Array(String)看业务怎么用。默认值处理也要小心。MySQL 的DEFAULT CURRENT_TIMESTAMP在 ClickHouse 里要写成DEFAULT now()。MySQL 的DEFAULT NULL在 ClickHouse 里如果字段是Nullable可以不写默认值默认就是 NULL。但要注意 ClickHouse 的Nullable字段在排序键里是不允许的如果某个字段既是Nullable又要做排序键就得在转换时特殊处理比如给它一个非空的默认值。注意ClickHouse 的Nullable类型会带来额外的存储开销和查询性能损耗。如果 MySQL 字段虽然允许 NULL 但业务上从不写 NULL转换时可以考虑去掉Nullable用默认值代替。这个决策要结合数据实际情况来定。4. 自定义 UDF 的实现细节与代码拆解4.1 元数据查询的 SQL 写法UDF 的第一步是把元数据查出来。查询information_schema.COLUMNS的 SQL 大概长这样SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT, ORDINAL_POSITION, DATETIME_PRECISION, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table ORDER BY ORDINAL_POSITION;这里有几个字段特别关键DATA_TYPE是基础类型如int、varcharCOLUMN_TYPE是完整类型如int(11) unsigned、varchar(255)。做类型映射时优先用COLUMN_TYPE判断因为它包含了UNSIGNED、长度、精度这些信息。DATA_TYPE可以作为辅助判断。DATETIME_PRECISION用来判断时间精度NUMERIC_PRECISION和NUMERIC_SCALE用来处理DECIMAL。CHARACTER_MAXIMUM_LENGTH是字符最大长度对VARCHAR有用虽然 ClickHouse 里会丢但可以记到注释里。表级信息查询information_schema.TABLESSELECT TABLE_NAME, TABLE_COMMENT, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table;TABLE_COMMENT用来生成 ClickHouse 表注释ENGINE一般用不上因为 ClickHouse 引擎要重新选但可以用来判断源表类型。4.2 类型映射函数的编写逻辑类型映射函数是整个 UDF 的核心。它的输入是 MySQL 的COLUMN_TYPE字符串输出是 ClickHouse 的类型字符串。用伪代码描述逻辑def map_type(column_type, data_type, datetime_precision, numeric_precision, numeric_scale): ct column_type.lower() is_unsigned unsigned in ct # 整数类型 if data_type tinyint: if ct tinyint(1): return UInt8 # 布尔语义 return UInt8 if is_unsigned else Int8 if data_type smallint: return UInt16 if is_unsigned else Int16 if data_type mediumint: return UInt32 if is_unsigned else Int32 if data_type int: return UInt32 if is_unsigned else Int32 if data_type bigint: return UInt64 if is_unsigned else Int64 # 浮点与定点 if data_type float: return Float32 if data_type double: return Float64 if data_type decimal: return fDecimal({numeric_precision}, {numeric_scale}) # 字符串 if data_type in (char, varchar, text, mediumtext, longtext, tinytext, blob, mediumblob, longblob, tinyblob): return String # 时间 if data_type date: return Date if data_type datetime or data_type timestamp: if datetime_precision and int(datetime_precision) 0: return fDateTime64({datetime_precision}) return DateTime # 枚举 if data_type enum: # 解析 COLUMN_TYPE 里的枚举值 values parse_enum_values(ct) if len(values) 127: return Enum8( , .join(f{v} {i1} for i, v in enumerate(values)) ) else: return Enum16( , .join(f{v} {i1} for i, v in enumerate(values)) ) # 兜底 return String这段逻辑里parse_enum_values需要从enum(a,b,c)这样的字符串里把值提取出来。可以用正则匹配单引号之间的内容。注意枚举值里可能包含转义的单引号处理时要小心。4.3 可空性与默认值的拼接规则类型映射完之后要处理可空性和默认值。ClickHouse 的字段定义格式是字段名 类型 [DEFAULT 表达式] [COMMENT 注释]如果字段可空类型要包一层Nullable()比如Nullable(String)。但前面说过Nullable有性能代价所以我的做法是默认保留可空性但提供一个开关允许调用方选择是否去掉Nullable。默认值的处理要分情况MySQL 的DEFAULT NULL如果字段是NullableClickHouse 里不用写默认值如果字段非空要写一个合理的默认值比如空字符串、0。MySQL 的DEFAULT CURRENT_TIMESTAMP映射成DEFAULT now()。MySQL 的数值默认值直接照搬比如DEFAULT 0。MySQL 的字符串默认值写成DEFAULT xxx。注释的处理相对简单直接把COLUMN_COMMENT拼到COMMENT xxx里。注意注释里的单引号要转义否则会破坏 SQL 语法。4.4 生成 ClickHouse 建表语句的模板把所有信息拼起来生成的 ClickHouse 建表语句模板大概是CREATE TABLE IF NOT EXISTS db_name.table_name ( id UInt32 COMMENT 主键, name String COMMENT 名称, amount Decimal(10, 2) COMMENT 金额, created_at DateTime64(3) COMMENT 创建时间 ) ENGINE MergeTree() ORDER BY id PARTITION BY toYYYYMM(created_at) COMMENT 业务表;这里有几个决策点引擎选择默认用MergeTree()这是最通用的。如果业务有去重需求可以用ReplacingMergeTree()如果需要聚合用SummingMergeTree()。这个通常不在自动转换里定而是由调用方指定或者根据表名规则推断。排序键ORDER BYClickHouse 的ORDER BY相当于主键决定了数据的物理排序和索引。选哪个字段做排序键直接影响查询性能。自动转换时可以默认用 MySQL 的主键字段或者用第一个字段。但更好的做法是让调用方指定因为排序键的选择需要结合查询模式。分区键PARTITION BY分区键决定数据怎么分片存储。常见做法是按时间分区比如toYYYYMM(created_at)。如果表里有时间字段可以自动生成如果没有就不分区。主键字段识别MySQL 的主键信息在information_schema.KEY_COLUMN_USAGE或者SHOW KEYS里。要拿到主键字段需要额外查一次。拿到之后可以作为ORDER BY的默认值。提示自动生成的ORDER BY和PARTITION BY只是默认值实际使用时一定要根据查询模式调整。排序键选错查询性能可能差几十倍。这个不能完全依赖自动化。5. 实操中踩过的坑与排查过程5.1 类型映射错误导致的建表失败第一次跑转换的时候遇到一张表建表直接报错。错误信息大概是Unknown data type family。排查过程是这样的先看生成的 DDL发现有个字段类型是Decimal(10, 0)。这个本身没问题但问题在于 MySQL 里那个字段是DECIMAL(10,0)而 ClickHouse 的Decimal要求精度参数必须是正数Decimal(10, 0)是合法的。再仔细看发现另一个字段是Decimal(0, 0)这是 MySQL 里DECIMAL没指定精度时的默认值但 ClickHouse 不接受Decimal(0,0)。修复方法是在映射函数里加判断如果NUMERIC_PRECISION为 0 或空就给一个默认精度比如Decimal(18, 4)。这个坑的教训是MySQL 的默认值和 ClickHouse 的约束不一定兼容映射时要处理边界情况。还有一个类似的坑是VARCHAR没指定长度。MySQL 里VARCHAR必须指定长度但有些工具生成的 DDL 可能不规范。如果CHARACTER_MAXIMUM_LENGTH为空映射成String没问题但如果代码里依赖这个值做判断就会出错。5.2 时区与精度丢失的隐蔽问题时区问题特别隐蔽。有一次迁移后发现查询出来的时间比实际时间少了 8 小时。排查了半天最后定位到MySQL 的DATETIME字段存的是本地时间ClickHouse 的DateTime默认按服务器时区解释如果服务器时区是 UTC就会差 8 小时。解决办法有两个一是在 ClickHouse 建表时显式指定时区写成DateTime(Asia/Shanghai)二是在数据同步时做时区转换。我倾向于第一种因为建表时就定好时区后续查询不用每次转换。精度丢失是另一个隐蔽问题。MySQL 的DATETIME(3)存了毫秒但转换时如果只映射成DateTime毫秒就被截断了。这个问题在数据同步后才会发现而且如果业务对毫秒不敏感可能很久都发现不了。所以转换时一定要检查DATETIME_PRECISION该用DateTime64就用。5.3 主键与排序键的语义差异MySQL 的主键和 ClickHouse 的排序键名字听起来像语义差别很大。MySQL 主键是唯一约束保证不重复ClickHouse 的ORDER BY只是排序和索引不保证唯一。如果直接把 MySQL 主键字段拿来做ORDER BY然后以为数据不会重复那就错了。ClickHouse 里要去重得用ReplacingMergeTree引擎而且去重是后台异步进行的查询时不一定已经去重完成。如果业务强依赖唯一性要么在写入前做去重要么用FINAL关键字查询但性能有代价。另一个差异是MySQL 主键可以是多字段组合ClickHouse 的ORDER BY也支持多字段写法是ORDER BY (col1, col2)。但顺序很重要ORDER BY (a, b)和ORDER BY (b, a)的索引效果完全不同。转换时如果直接照搬主键字段顺序可能不是最优的需要根据查询模式调整。5.4 批量转换时的性能与顺序问题单表转换没问题但批量转换几百张表时遇到两个问题。第一个是查询元数据的性能。如果每张表都单独查一次information_schema几百张表就是几百次查询很慢。优化方法是一次性把整个库的字段信息查出来然后在内存里按表名分组处理。SQL 改成WHERE TABLE_SCHEMA your_db不加TABLE_NAME条件一次拿全库。第二个是表之间的依赖顺序。如果表之间有外键依赖建表顺序就很重要。ClickHouse 不支持外键所以外键约束在转换时会丢掉但建表顺序如果不对可能影响后续的数据同步。我的做法是先建所有表再同步数据不依赖建表顺序。如果确实需要顺序可以按表名排序或者按依赖关系拓扑排序。问题类型现象根因解决方案类型映射失败建表报 Unknown data typeDecimal 精度为 0加默认精度兜底时区偏差时间差 8 小时服务器时区不一致建表时显式指定时区精度丢失毫秒被截断未用 DateTime64检查 DATETIME_PRECISION数据重复以为主键唯一ORDER BY 不保证唯一用 ReplacingMergeTree 或写入前去重批量转换慢几百张表查询久逐表查询元数据一次查全库再分组6. 让转换结果更可用的几个进阶技巧6.1 把 MySQL 索引信息转成 ClickHouse 跳数索引MySQL 的二级索引在 ClickHouse 里没有直接对应但 ClickHouse 有跳数索引skip index可以在某些场景下替代。比如 MySQL 里对status字段建了索引ClickHouse 里可以建一个minmax或set类型的跳数索引。转换时可以从information_schema.STATISTICS里读出索引信息然后根据索引类型生成对应的跳数索引。不过这个不是必须的因为 ClickHouse 的查询性能和 MySQL 差异很大MySQL 的索引策略不一定适用于 ClickHouse。我的建议是先不转索引等实际查询跑起来根据慢查询再补跳数索引。这样更有针对性。6.2 注释与字段顺序的保留策略字段注释一定要保留这个对后续维护太重要了。ClickHouse 的字段注释写在类型后面格式是COMMENT 注释内容。转换时直接把COLUMN_COMMENT拼进去就行注意转义单引号。字段顺序也要保留按ORDINAL_POSITION排序。ClickHouse 虽然不强制字段顺序但保持和 MySQL 一致对开发和排查问题都方便。特别是做数据对比时字段顺序一致能省很多事。表注释同理从TABLE_COMMENT取拼到建表语句最后。6.3 生成结果的人工复核清单自动转换再完善也不能保证 100% 正确。生成 DDL 后建议按这个清单人工过一遍检查所有Nullable字段确认是否真的需要可空检查ORDER BY字段确认是否符合查询模式检查PARTITION BY字段确认分区粒度是否合理检查时间字段确认时区和精度是否正确检查Decimal字段确认精度和标度是否匹配业务检查枚举字段确认映射方式是否符合预期检查默认值确认没有语法错误这个清单看起来繁琐但跑几次之后就形成肌肉记忆了。比起建表后发现问题再改提前检查的成本低得多。6.4 把转换逻辑封装成可复用组件如果这套转换要反复用建议把它封装成一个独立的组件或服务。输入是 MySQL 连接信息和表名列表输出是 ClickHouse DDL 列表。组件内部把元数据查询、类型映射、DDL 生成都封装好对外只暴露简单接口。封装时要注意配置化类型映射规则、默认引擎、默认排序键策略这些都做成可配置的。这样不同业务线可以根据自己的需求调整不用改代码。配置可以用 JSON 或 YAML 文件管理改起来方便。我在实际项目里把这套逻辑封装成了一个命令行工具支持单表转换和批量转换输出可以直接重定向到文件或者管道给 ClickHouse 客户端执行。用起来很顺手新接入一个库几分钟就能生成全部建表语句。提示封装组件时一定要把生成 DDL和执行 DDL分开。生成是只读操作可以随便跑执行是写操作要谨慎。分开之后可以先 review 生成的 DDL确认无误再执行避免自动执行出错。7. 关于这套方案的一些个人体会这套 UDF 转换方案我用了一段时间最大的感受是自动化能解决 80% 的重复劳动但剩下 20% 的判断必须人工介入。类型映射、字段可空性这些可以自动处理但排序键选择、分区策略、引擎选择这些必须结合具体业务来定。指望一个工具全自动搞定最后往往要花更多时间修问题。另一个体会是转换规则要随着使用不断迭代。刚开始可能只覆盖了常见类型用着用着遇到特殊类型就补一条规则。时间长了规则库越来越完善转换准确率越来越高。这个过程是正常的不要指望一次写完美。最后分享一个小技巧转换生成的 DDL可以先在测试环境建表然后跑一遍数据同步对比 MySQL 和 ClickHouse 的查询结果。如果结果一致说明转换没问题如果不一致大概率是类型映射或时区处理有问题。这个验证步骤虽然费时间但能提前发现很多隐蔽问题值得做。