从MySQL到PostgreSQLNestJS中TypeORM实体迁移的类型陷阱与实战指南当团队决定将NestJS项目的数据库从MySQL迁移到PostgreSQL时实体层的重构往往成为最棘手的环节之一。TypeORM虽然提供了跨数据库支持但不同数据库在类型系统、功能实现上的差异常常导致迁移过程中出现各种暗坑。本文将深入剖析这些类型差异并提供可落地的解决方案。1. 枚举类型的跨数据库适配策略MySQL和PostgreSQL对枚举类型的实现可谓大相径庭。在MySQL中枚举类型是原生支持的数据库特性// MySQL中的枚举定义 Column({ type: enum, enum: [pending, approved, rejected], default: pending }) status: string;而PostgreSQL虽然也有枚举类型但需要先创建自定义类型-- PostgreSQL中必须先创建枚举类型 CREATE TYPE status_enum AS ENUM (pending, approved, rejected);对应的TypeORM实体应该调整为// PostgreSQL兼容的枚举定义 Column({ type: enum, enum: [pending, approved, rejected], default: pending, enumName: status_enum // 关键差异点 }) status: string;注意PostgreSQL的枚举类型在迁移后需要手动创建TypeORM不会自动生成这些自定义类型。更健壮的实现方案是使用联合类型检查约束// 类型安全的替代方案 type Status pending | approved | rejected; Column({ type: varchar, default: pending }) status: Status;然后在迁移脚本中添加检查约束ALTER TABLE your_table ADD CONSTRAINT status_check CHECK (status IN (pending, approved, rejected));2. JSON处理的艺术与陷阱MySQL和PostgreSQL对JSON的支持程度差异显著特性MySQL 5.7PostgreSQL 9.2原生JSON类型支持支持JSON路径查询有限支持完整支持JSONB二进制存储不支持支持索引支持有限完整在TypeORM中MySQL通常使用simple-json类型// MySQL常见的JSON存储方式 Column(simple-json) profile: { name: string; preferences: any };而PostgreSQL可以直接使用更强大的jsonb类型// PostgreSQL推荐的JSON处理方式 Column(jsonb, { nullable: true }) profile: { name: string; preferences: any };实际迁移时需要注意数据序列化差异MySQL的simple-json会进行二次JSON.stringify而PostgreSQL的jsonb直接存储原始对象查询语法不同PostgreSQL支持丰富的JSON操作符-,-,#等性能考量jsonb支持GIN索引适合复杂查询场景// PostgreSQL特有的JSON查询示例 const user await repository.findOne({ where: { profile: { name: John // 支持对象形式的查询 } } });3. 时间类型的时区处理难题时间类型是数据库迁移中最容易出问题的领域之一。两种数据库在时间处理上的主要差异MySQL的timestamp:存储为UTC时间检索时转换为当前会话时区受系统变量time_zone影响PostgreSQL的timestamp:没有隐式时区转换分为timestamp(无时区)和timestamptz(有时区)行为更明确但需要显式处理TypeORM实体适配建议// 跨数据库兼容的时间定义 Column({ type: timestamp, precision: 3, default: () CURRENT_TIMESTAMP(3) }) createdAt: Date; // 带时区的情况 Column({ type: timestamptz, // PostgreSQL特有 default: () CURRENT_TIMESTAMP }) updatedAt: Date;迁移时的关键检查点确保所有timestamp列在MySQL中存储的是UTC时间考虑使用timestamptz明确时区信息测试边界情况如夏令时切换时刻-- 迁移后验证SQL示例 SELECT column_name, data_type, datetime_precision FROM information_schema.columns WHERE table_name your_table;4. 主键策略与高级类型实战自增ID vs UUIDMySQL常见的自增ID在PostgreSQL中有不同的实现方式// MySQL风格的自增ID PrimaryGeneratedColumn() id: number; // PostgreSQL更推荐的做法 PrimaryGeneratedColumn(uuid) id: string;性能对比指标自增IDUUID插入性能更快稍慢分布式友好不适合理想可猜测性连续可猜测随机不可猜测索引效率更高稍低PostgreSQL特有类型的应用hstore类型- 键值对存储// 首先安装pg-hstore包 import * as hstore from pg-hstore; Column({ type: hstore, transformer: { from: hstore.parse, to: hstore.stringify } }) properties: Recordstring, string;数组类型- PostgreSQL原生支持// 字符串数组 Column(text, { array: true }) tags: string[]; // 整数数组 Column(int, { array: true }) scores: number[];几何类型- 空间数据支持Column(geometry, { spatialFeatureType: Point, srid: 4326 }) location: Point;5. 迁移实战检查清单为确保平滑迁移建议按照以下步骤操作数据库差异分析使用typeorm schema:log生成当前Schema对比MySQL与PostgreSQL的DDL差异类型映射检查// 常见类型映射参考 const typeMapping { tinyint: smallint, mediumint: integer, longtext: text, datetime: timestamp, enum: enum或varcharcheck约束 };数据迁移脚本# 使用pgloader工具迁移数据 pgloader mysql://user:passlocalhost/dbname postgresql://user:passlocalhost/dbname测试验证重点枚举类型的插入和查询JSON字段的深度查询时间字段的时区处理事务和锁行为差异性能优化调整PostgreSQL的VACUUM ANALYZE索引重建查询计划分析// 迁移后的健全性检查代码示例 async function validateMigration(connection: Connection) { const mismatchedTypes await connection.query( SELECT column_name, data_type FROM information_schema.columns WHERE table_name your_table AND data_type NOT IN (integer, text, timestamp,...) ); if (mismatchedTypes.length 0) { throw new Error(发现类型不匹配的列: ${JSON.stringify(mismatchedTypes)}); } }在最近的一个电商平台迁移项目中团队花了三周时间才解决所有类型兼容性问题。最棘手的发现是MySQL的DATETIME默认值在PostgreSQL中表现不同导致订单状态更新时间出现偏差。最终我们采用了统一使用CURRENT_TIMESTAMP的显式定义并在应用层加强时间处理的单元测试。
