当前位置：首页 > news >正文

MySQL转PostgreSQL迁移实战：从语法错误到完美兼容

news 2025/8/2 14:26:01

MySQL转PostgreSQL迁移实战：从语法错误到完美兼容

前言

在数据库迁移项目中，从MySQL转换到PostgreSQL是一个常见但充满挑战的任务。最近我在一个项目中遇到了这样的需求，在转换过程中遇到了各种语法错误和兼容性问题。本文将详细记录整个修复过程，希望能为遇到类似问题的开发者提供参考。

问题背景

项目需要将现有的MySQL数据库迁移到PostgreSQL，原始SQL文件包含了大量的表结构定义、索引、约束和数据。在直接执行转换后的SQL文件时，遇到了各种语法错误，需要逐一修复。

遇到的主要问题及解决方案

1. 时间戳语法差异

问题描述：

-- MySQL语法
time_stamp_ NULL NOT NULL DEFAULT CURRENT_TIMESTAMP(3)

错误信息：

错误: 语法错误 在 "None" 或附近的
LINE 8: time_stamp_ NULL NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

问题分析：
PostgreSQL不支持CURRENT_TIMESTAMP(3)这种带精度参数的时间戳语法，同时字段名中包含NULL也会导致语法冲突。

解决方案：

-- PostgreSQL语法
"time_stamp_NULL" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

修复代码：

# 修复时间戳精度问题
content = re.sub(r'CURRENT_TIMESTAMP\(\d+\)', 'CURRENT_TIMESTAMP', content)
content = re.sub(r'(\w+_NULL)\s+', r'"\1" ', content)

2. 自增主键语法差异

问题描述：

-- MySQL语法
id bigint NOT NULL AUTO_INCREMENT

解决方案：

-- PostgreSQL语法
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY

修复代码：

content = re.sub(r'bigint NOT NULL AUTO_INCREMENT', 'bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY', content)

3. 数据类型映射问题

MySQL和PostgreSQL在数据类型上有很大差异，需要进行映射转换：

MySQL类型	PostgreSQL类型	说明
`tinyint`	`int2`	16位整数
`smallint`	`int2`	16位整数
`int`	`int4`	32位整数
`bigint`	`int8`	64位整数
`datetime`	`timestamp`	时间戳
`text`	`text`	文本类型
`blob`	`bytea`	二进制数据
`bit`	`bit`	位类型
`boolean`	`boolean`	布尔类型

修复代码：

type_mappings = {r'\btinyint\b': 'int2',r'\bsmallint\b': 'int2',r'\bint\b': 'int4',r'\bbigint\b': 'int8',r'\bdatetime\b': 'timestamp',r'\bblob\b': 'bytea',# ... 更多映射
}for mysql_type, pg_type in type_mappings.items():content = re.sub(mysql_type, pg_type, content, flags=re.IGNORECASE)

4. 字段名和保留字冲突

问题描述：

-- 错误语法
DROP "table" "if" EXISTS dual

错误信息：

错误: 语法错误 在 ""table"" 或附近的
LINE 14: DROP "table" "if" EXISTS dual

问题分析：
自动转换工具过于激进，将SQL关键字也用双引号包围了，导致语法错误。

解决方案：

-- 正确语法
DROP TABLE IF EXISTS dual

修复代码：

# 修复被错误引用的SQL关键字
content = re.sub(r'DROP "table"', 'DROP TABLE', content)
content = re.sub(r'DROP "if"', 'DROP IF', content)
content = re.sub(r'"if" EXISTS', 'IF EXISTS', content)
content = re.sub(r'CREATE "table"', 'CREATE TABLE', content)

5. 缺失列问题

某些表缺少必要的列，导致应用运行时出错：

问题表及缺失列：

sys_coffee 表缺少 deleted 和 tenant_id 列
erp_product 表缺少 brand_id 和 skus 列
erp_product_brand 表缺少 brand_category 列
erp_product_reagent 表缺少 skus 列

修复代码：

missing_columns = {'sys_coffee': ['deleted int2 DEFAULT 0', 'tenant_id int4'],'erp_product': ['brand_id bigint', 'skus text'],'erp_product_brand': ['brand_category varchar(255)'],'erp_product_reagent': ['skus text']
}for table_name, columns in missing_columns.items():pattern = rf'(CREATE TABLE {table_name} \([\s\S]*?)(PRIMARY KEY \(id\));'for column in columns:replacement = rf'\1  {column},\n\2;'content = re.sub(pattern, replacement, content)

6. 布尔值语法差异

问题描述：

-- MySQL语法
deleted bit(1) DEFAULT b'0'

解决方案：

-- PostgreSQL语法
deleted boolean DEFAULT '0'

修复代码：

content = re.sub(r"b'0'", "'0'", content)
content = re.sub(r"b'1'", "'1'", content)
content = re.sub(r"DEFAULT b'0'", "DEFAULT '0'", content)
content = re.sub(r"DEFAULT b'1'", "DEFAULT '1'", content)

7. 字符集和存储引擎语法

问题描述：

-- MySQL语法
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '表注释' ROW_FORMAT = DYNAMIC;

解决方案：

-- PostgreSQL语法
);

修复代码：

content = re.sub(r'ENGINE = \w+.*?ROW_FORMAT = \w+;', ';', content)
content = re.sub(r'CHARACTER SET \w+ COLLATE \w+', '', content)

8. 序列管理

PostgreSQL需要显式创建序列来支持自增主键：

解决方案：

-- 为每个表创建序列
CREATE SEQUENCE IF NOT EXISTS table_name_id_seq;
ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('table_name_id_seq');
ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id;

修复代码：

table_names = re.findall(r'CREATE TABLE (\w+)', content)
for table_name in table_names:if not table_name.startswith(('act_', 'flw_', 'qrtz_')):sequence_statements.append(f"""
CREATE SEQUENCE IF NOT EXISTS {table_name}_id_seq;
ALTER TABLE {table_name} ALTER COLUMN id SET DEFAULT nextval('{table_name}_id_seq');
ALTER SEQUENCE {table_name}_id_seq OWNED BY {table_name}.id;
""")

修复脚本架构

为了系统性地解决这些问题，我创建了一套修复脚本：

1. 基础修复脚本 (`fix_postgres_sql.py`)

处理基本的语法转换
添加缺失的列
数据类型映射

2. 全面修复脚本 (`comprehensive_fix.py`)

包含所有基础修复功能
添加序列创建
处理更多语法问题

3. 时间戳修复脚本 (`fix_timestamp.py`)

专门修复时间戳相关问题
处理精度参数问题
修复字段名冲突

4. 字段名修复脚本 (`fix_field_names.py`)

处理字段名和保留字冲突
修复被错误引用的标识符

5. 特定问题修复脚本 (`fix_specific_issues.py`)

修复具体的语法错误
处理SQL关键字引用问题

6. 验证脚本 (`verify_fix.py`)

验证修复效果
检查遗留问题
生成修复报告

7. 完整修复脚本 (`complete_fix.py`)

整合所有修复功能
按步骤执行修复
提供详细进度报告

使用示例

# 快速修复（推荐）
python complete_fix.py input.sql output.sql# 分步修复
python fix_postgres_sql.py input.sql output1.sql
python comprehensive_fix.py output1.sql output2.sql
python fix_timestamp.py output2.sql output3.sql
python verify_fix.py output3.sql