MySQL到KaiwuDB数据类型映射全解析：避坑指南

文章目录每日一句正能量一、前言数据类型是迁移的基石二、数据类型对比总览2.1 整数类型2.2 浮点类型2.3 字符串类型2.4 日期时间类型2.5 JSON类型2.6 枚举和集合类型三、特殊类型处理3.1 自增主键3.2 位类型3.3 空间类型四、类型映射工具4.1 手动映射4.2 自动映射KDTS4.3 脚本自动映射五、常见问题排查5.1 数据截断5.2 精度丢失5.3 时区问题六、最佳实践6.1 迁移前检查6.2 迁移后验证6.3 类型映射检查清单七、总结每日一句正能量理解不是妥协而是打开格局的钥匙。看清事物因果与逻辑。钥匙打开了门让你看清全貌但进不进去、如何行动主动权依然在你。一、前言数据类型是迁移的基石前面四篇文章我分享了MySQL迁移KaiwuDB的整体流程、KDTS图形化工具、DataX脚本化迁移、CSV离线兜底方案。有读者反馈“迁移过程中最头疼的不是工具使用而是数据类型不匹配导致的各种报错。”确实数据类型映射是数据库迁移中最基础也最容易出问题的环节。MySQL和KaiwuDB虽然都兼容SQL标准但在具体实现上存在差异。一个小小的类型不匹配可能导致数据截断、精度丢失甚至迁移失败。本文就把MySQL到KaiwuDB的数据类型映射完整梳理一遍包括类型差异对比、常见陷阱、解决方案以及我踩过的坑。二、数据类型对比总览2.1 整数类型MySQL类型KaiwuDB类型存储范围注意事项TINYINTINT2-128 ~ 127直接映射TINYINT UNSIGNEDINT20 ~ 255直接映射SMALLINTINT2-32768 ~ 32767直接映射SMALLINT UNSIGNEDINT40 ~ 65535需放大到INT4MEDIUMINTINT4-8388608 ~ 8388607直接映射MEDIUMINT UNSIGNEDINT40 ~ 16777215需放大到INT4INTINT4-2147483648 ~ 2147483647直接映射INT UNSIGNEDINT80 ~ 4294967295需放大到INT8BIGINTINT8-9223372036854775808 ~ 9223372036854775807直接映射BIGINT UNSIGNEDNUMERIC(20)0 ~ 18446744073709551615必须映射为NUMERIC(20)踩坑点1BIGINT UNSIGNED映射错误-- MySQLCREATETABLEtest(idBIGINTUNSIGNEDPRIMARYKEY);-- 错误直接映射为INT8CREATETABLEtest(id INT8PRIMARYKEY-- 错误会溢出);-- 正确映射为NUMERIC(20)CREATETABLEtest(idNUMERIC(20)PRIMARYKEY);原因KaiwuDB的INT8最大值为9223372036854775807而MySQL的BIGINT UNSIGNED最大值为18446744073709551615直接映射会导致溢出。2.2 浮点类型MySQL类型KaiwuDB类型精度注意事项FLOATFLOAT4单精度直接映射DOUBLEFLOAT8双精度直接映射DECIMAL(M,D)DECIMAL(M,D)定点数直接映射NUMERIC(M,D)NUMERIC(M,D)定点数直接映射踩坑点2DECIMAL精度丢失-- MySQLCREATETABLEtest(amountDECIMAL(18,2));-- KaiwuDB正确CREATETABLEtest(amountDECIMAL(18,2));-- 错误改为FLOAT8CREATETABLEtest(amount FLOAT8-- 错误会丢失精度);原因FLOAT8是浮点数无法精确表示小数会导致金额计算错误。DECIMAL是定点数可以精确表示小数。2.3 字符串类型MySQL类型KaiwuDB类型最大长度注意事项CHAR(N)CHAR(N)N字符直接映射VARCHAR(N)VARCHAR(N)N字符直接映射TINYTEXTTEXT255字节直接映射TEXTTEXT65535字节直接映射MEDIUMTEXTTEXT16777215字节直接映射LONGTEXTTEXT无限制直接映射BINARY(N)BYTESN字节直接映射VARBINARY(N)VARBYTESN字节直接映射TINYBLOBBYTES255字节直接映射BLOBBYTES65535字节直接映射MEDIUMBLOBBYTES16777215字节直接映射LONGBLOBBYTES无限制直接映射踩坑点3TEXT字段长度超限-- MySQLCREATETABLEtest(contentLONGTEXT);-- KaiwuDB正确CREATETABLEtest(contentTEXT-- TEXT在KaiwuDB中无长度限制);注意KaiwuDB的TEXT类型没有长度限制可以直接映射MySQL的LONGTEXT。2.4 日期时间类型MySQL类型KaiwuDB类型精度注意事项DATEDATE日期直接映射TIMETIME时间直接映射DATETIMETIMESTAMP日期时间注意时区TIMESTAMPTIMESTAMPTZ日期时间时区直接映射YEARINT2年份映射为INT2踩坑点4DATETIME时区问题-- MySQLCREATETABLEtest(created_atDATETIME);-- KaiwuDB正确CREATETABLEtest(created_atTIMESTAMP-- 注意KaiwuDB的TIMESTAMP会自动处理时区);-- 如果需要保留原始时区信息CREATETABLEtest(created_at TIMESTAMPTZ-- 带时区的TIMESTAMP);原因MySQL的DATETIME不存储时区信息而KaiwuDB的TIMESTAMP会转换为UTC存储。如果业务需要保留原始时区应使用TIMESTAMPTZ。2.5 JSON类型MySQL类型KaiwuDB类型说明注意事项JSONJSONJSON数据直接映射踩坑点5JSON字段长度超限-- MySQLCREATETABLEtest(ext JSON);-- KaiwuDB正确CREATETABLEtest(ext JSON);-- 如果JSON数据过大CREATETABLEtest(extTEXT-- 映射为TEXT避免长度限制);原因KaiwuDB的JSON类型有默认长度限制如果JSON数据过大可以映射为TEXT类型。2.6 枚举和集合类型MySQL类型KaiwuDB类型说明注意事项ENUMVARCHAR CHECK枚举值需要手动创建CHECK约束SETARRAY或JSON集合值需要转换踩坑点6ENUM类型映射-- MySQLCREATETABLEtest(statusENUM(active,inactive,deleted));-- KaiwuDB正确CREATETABLEtest(statusVARCHAR(20)CHECK(statusIN(active,inactive,deleted)));原因KaiwuDB不支持ENUM类型需要使用VARCHAR CHECK约束实现。三、特殊类型处理3.1 自增主键MySQLKaiwuDB说明AUTO_INCREMENT不支持需要应用层生成或使用序列解决方案-- MySQLCREATETABLEorders(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,order_noVARCHAR(64));-- KaiwuDB使用序列CREATESEQUENCE orders_id_seqSTART1INCREMENT1;CREATETABLEorders(idNUMERIC(20)DEFAULTnextval(orders_id_seq)PRIMARYKEY,order_noVARCHAR(64));-- 或者应用层生成IDCREATETABLEorders(idNUMERIC(20)PRIMARYKEY,order_noVARCHAR(64));3.2 位类型MySQLKaiwuDB说明BIT(N)INT2/INT4/INT8根据位数选择解决方案-- MySQLCREATETABLEtest(flagsBIT(8));-- KaiwuDBCREATETABLEtest(flags INT2-- 8位用INT2);3.3 空间类型MySQLKaiwuDB说明GEOMETRY不支持需要转换POINT不支持需要转换LINESTRING不支持需要转换POLYGON不支持需要转换解决方案-- MySQLCREATETABLEtest(locationPOINT);-- KaiwuDB使用JSON存储CREATETABLEtest(location JSON-- 存储为{x: 123.45, y: 67.89});-- 或者使用两个字段CREATETABLEtest(location_x FLOAT8,location_y FLOAT8);四、类型映射工具4.1 手动映射根据上面的对照表手动编写CREATE TABLE语句。4.2 自动映射KDTSKDTS会自动识别MySQL的类型并映射到KaiwuDB但需要人工确认。4.3 脚本自动映射#!/usr/bin/env python3# mysql_to_kaiwudb_types.pymysql_to_kaiwudb{TINYINT:INT2,SMALLINT:INT2,MEDIUMINT:INT4,INT:INT4,BIGINT:INT8,FLOAT:FLOAT4,DOUBLE:FLOAT8,DECIMAL:DECIMAL,NUMERIC:NUMERIC,CHAR:CHAR,VARCHAR:VARCHAR,TEXT:TEXT,BLOB:BYTES,DATE:DATE,TIME:TIME,DATETIME:TIMESTAMP,TIMESTAMP:TIMESTAMPTZ,JSON:JSON,ENUM:VARCHAR,SET:ARRAY,BIT:INT2,}defmap_type(mysql_type,unsignedFalse):映射MySQL类型到KaiwuDB类型base_typemysql_type.upper().split(()[0]ifbase_typeBIGINTandunsigned:returnNUMERIC(20)ifbase_typeINTandunsigned:returnINT8returnmysql_to_kaiwudb.get(base_type,TEXT)# 测试print(map_type(BIGINT,unsignedTrue))# NUMERIC(20)print(map_type(INT,unsignedTrue))# INT8print(map_type(VARCHAR(64)))# VARCHARprint(map_type(DECIMAL(18,2)))# DECIMAL五、常见问题排查5.1 数据截断问题导入时数据被截断ERROR: value too long for type character varying(64)解决方案-- 检查源数据最大长度SELECTMAX(LENGTH(order_no))FROMorders;-- 调整目标字段长度ALTERTABLEordersALTERCOLUMNorder_noTYPEVARCHAR(128);5.2 精度丢失问题金额计算出现精度丢失-- 错误使用FLOAT8SELECTSUM(amount)FROMorders;-- 结果123456789.01000001-- 正确使用DECIMALSELECTSUM(amount)FROMorders;-- 结果123456789.01解决方案金额字段必须使用DECIMAL类型。5.3 时区问题问题时间显示不一致-- MySQLSELECTcreated_atFROMorders;-- 结果2023-01-01 10:00:00-- KaiwuDBSELECTcreated_atFROMorders;-- 结果2023-01-01 02:00:0000解决方案-- 设置时区SETTIMEZONEAsia/Shanghai;-- 或者使用TIMESTAMPTZALTERTABLEordersALTERCOLUMNcreated_atTYPETIMESTAMPTZ;六、最佳实践6.1 迁移前检查-- 检查MySQL表结构SHOWCREATETABLEorders;-- 检查字段类型和长度SELECTcolumn_name,data_type,character_maximum_length,numeric_precision,numeric_scaleFROMinformation_schema.columnsWHEREtable_nameorders;-- 检查数据范围SELECTMIN(id)asmin_id,MAX(id)asmax_id,MAX(LENGTH(order_no))asmax_order_no_lengthFROMorders;6.2 迁移后验证-- 检查KaiwuDB表结构SHOWCREATETABLEorders;-- 检查数据类型SELECTcolumn_name,data_type,character_maximum_lengthFROMinformation_schema.columnsWHEREtable_nameorders;-- 抽样验证SELECT*FROMordersWHEREid10086;6.3 类型映射检查清单整数类型检查UNSIGNEDBIGINT UNSIGNED映射为NUMERIC(20)浮点类型金额字段使用DECIMAL避免使用FLOAT/DOUBLE字符串类型检查长度VARCHAR(N)直接映射日期时间检查时区DATETIME映射为TIMESTAMPJSON类型检查长度过大时映射为TEXT枚举类型使用VARCHAR CHECK约束自增主键使用序列或应用层生成七、总结数据类型映射是数据库迁移的基础也是最容易出问题的环节。通过本文的介绍你应该能够理解类型差异MySQL和KaiwuDB的类型对应关系避免常见陷阱BIGINT UNSIGNED、DECIMAL精度、时区等使用映射工具手动映射、KDTS自动映射、脚本映射验证迁移结果检查表结构、抽样验证、性能测试关键要点BIGINT UNSIGNED必须映射为NUMERIC(20)金额字段必须使用DECIMAL避免使用FLOAT/DOUBLEDATETIME注意时区问题使用TIMESTAMPTZ保留时区JSON字段过长时映射为TEXTENUM类型使用VARCHAR CHECK约束如果你正在考虑数据库国产化迁移建议先做好类型映射的规划避免迁移过程中出现数据问题。转载自https://blog.csdn.net/u014727709/article/details/163783813欢迎 点赞✍评论⭐收藏欢迎指正