pg相比mysql优点：1 性能更好 2 支持各种丰富插件，可以实现时序数据库、向量数据库、GIS数据库等功能，减少中间件维护工作量。最近因为项目需要，我把项目用的mysql数据库迁移到了postgresql数据库，这里分享一下改造的经验。

改造前准备工具

1 豆包： 用它来帮忙进行语句转换，生成脚本，检查代码非常方便。

2 navicat：支持mysql数据自动迁移到pg，可以节省大量的库表ddl迁移工作。

3 idea：idea提供非常实用的搜索功能，支持正则查找和替换，而且可以指定文件类型查找，甚至可以指定在字符串中查找，忽略注释等。注意改造前，建议把“设置->高级设置->在“在文件中查找/显示用法”预览中显示的最大结果数”的选项改大点，默认是100，我改成500。这样搜索结果展示更全，更方便进行综合分析。

4 git bash：用它来执行grep批量提取数据，有一些复杂替换工作也需要写成shell脚本让它执行

下面我分步介绍一下改造工作：

库表结构迁移

这一步我用的是navicat的数据传输功能，它可以把mysql数据库的表和数据转换到postgresql数据库上。不过转换过程中，会丢失库表主键和字段默认值。需要在转换后手工补上。

可以通过sql批量生成ddl语句。

-- 在MySQL中执行此查询，生成PostgreSQL可用的创建主键SQL
SELECT 
    CONCAT(
        'ALTER TABLE "数据库模式名称_data"."public"."', 
        kcu.TABLE_NAME, 
        '" ADD PRIMARY KEY ("', 
        GROUP_CONCAT(kcu.COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION SEPARATOR '", "'), 
        '");'
    ) AS pg_alter_sql
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND tc.TABLE_NAME = kcu.TABLE_NAME
WHERE 
    tc.TABLE_SCHEMA = '数据库模式名称'
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND kcu.TABLE_NAME NOT LIKE 'QRTZ%'  -- 排除QRTZ开头的表
GROUP BY 
    kcu.TABLE_NAME
ORDER BY 
    kcu.TABLE_NAME;
    
    
-- 生成PostgreSQL添加默认值的最终版SQL
SELECT 
    CONCAT(
        'ALTER TABLE "数据库模式名称_data"."public"."', TABLE_NAME, '" ',
        'ALTER COLUMN "', COLUMN_NAME, '" ',
        'SET DEFAULT ',
        -- 精准处理各类默认值
        CASE 
            -- 1. 时间函数（不加引号）
            WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 'CURRENT_TIMESTAMP'
            
            -- 2. 位类型（MySQL的b'0'转PG的B'0'）
            WHEN COLUMN_DEFAULT LIKE 'b''%''' THEN REPLACE(COLUMN_DEFAULT, 'b''', 'B''')
            
            -- 3. 字符串类型（含空字符串""）
            WHEN DATA_TYPE IN ('varchar', 'char', 'text') THEN 
                -- 空字符串""转''，普通字符串转'内容'，并转义内部单引号
                CONCAT("'", REPLACE(COLUMN_DEFAULT, "'", "''"), "'")
            
            -- 4. 数字/其他类型（直接使用，不加引号）
            ELSE COLUMN_DEFAULT 
        END,
        ';'
    ) AS pg_alter_default_sql
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = '数据库模式名称'
    AND TABLE_NAME NOT LIKE 'QRTZ%'
    AND COLUMN_DEFAULT IS NOT NULL
ORDER BY 
    TABLE_NAME, ORDINAL_POSITION;

语法兼容修改

这一步工作是最复杂的，我花了一周多时间，才把查询类的语法兼容都改完。这还是因为大多数sql是兼容的，特别是mybatis plus生成的sql几乎不用改。要不然这个一百多万行代码的项目，不知道要改到猴年马月去。

mysql和pg不兼容的语法点很多，我让几个ai列都没列全。这里我只介绍我们项目中碰上比较多不兼容的地方。

对``的支持

mysql支持用``包裹表名或者字段名（如果表或者字段名是关键字，则必须要求这样做），而 PostgreSQL 不支持，如果表或者字段名是保留关键字需用"" 包裹（只有保留关键字需要，非保留关键字不需要）。我用sql查了一下，发现我们系统里面只有几个字段是保留关键字。

-- 统计当前模式下，表名和字段名中包含的保留关键字数量及详情
WITH reserved_keywords AS (
  -- 获取所有 PostgreSQL 保留关键字（转为小写，统一比对）
  SELECT LOWER(word) AS keyword
  FROM pg_get_keywords()
  WHERE catcode = 'R'  -- 'R' 表示保留关键字
),
all_table_names AS (
  -- 获取当前模式下的所有表名（转为小写）
  SELECT 
    table_name,
    LOWER(table_name) AS table_name_lower
  FROM information_schema.tables
  WHERE table_schema = CURRENT_SCHEMA()  -- 当前模式
    AND table_type = 'BASE TABLE'  -- 只考虑普通表（排除视图等）
),
all_column_names AS (
  -- 获取当前模式下的所有字段名（转为小写）
  SELECT 
    table_name,
    column_name,
    LOWER(column_name) AS column_name_lower
  FROM information_schema.columns
  WHERE table_schema = CURRENT_SCHEMA()  -- 当前模式
)
-- 统计并列出表名和字段名中包含的保留关键字
SELECT 
  '表名' AS object_type,
  table_name AS object_name,
  keyword AS reserved_keyword
FROM all_table_names
JOIN reserved_keywords ON table_name_lower = keyword

UNION ALL

SELECT 
  '字段名' AS object_type,
  CONCAT(table_name, '.', column_name) AS object_name,
  keyword AS reserved_keyword
FROM all_column_names
JOIN reserved_keywords ON column_name_lower = keyword

ORDER BY object_type, object_name;

在idea里面使用替换功能，将“([^]+?)`”替换成“$1”，然后再针对少量的保留关键字字段做处理，增加""即可。另外我们程序里面有一些自己写的sql分析功能，里面需要使用"``"判断列名，也需要改成使用“”。

日期相关函数处理

mysql里面有很多日期函数，在pg里面是不兼容的。我的做法是一部分函数在pg里面创建同名兼容函数实现。一部分则使用shell脚本进行替换。

例如：TIMESTAMPDIFF函数可以用下面shell脚本替换

sed -E   's/TIMESTAMPDIFF\( *[Mm][Ii][Nn][Uu][Tt][Ee] *,[[:space:]]*([^,]+)[[:space:]]*,[[:space:]]*(.*?)\)/FLOOR(EXTRACT(EPOCH FROM (\2 - \1)) \/ 60)/g' 

date_format和date_sub、date_add、curdate函数我是通过同名函数兼容的。

CREATE OR REPLACE FUNCTION date_format(
    date_val TIMESTAMPTZ,
    format_str TEXT
) RETURNS TEXT AS $
DECLARE
    formatted TEXT;
BEGIN
    -- 处理字面量 %（MySQL 中 %% 表示 %）
    formatted := REPLACE(format_str, '%%', '%%TEMP%%');
    
    -- 按顺序替换所有格式符
    formatted := REPLACE(formatted, '%Y', 'YYYY');
    formatted := REPLACE(formatted, '%y', 'YY');
    formatted := REPLACE(formatted, '%m', 'MM');
    formatted := REPLACE(formatted, '%c', 'FMMM');  -- 月份无前导零
    formatted := REPLACE(formatted, '%d', 'DD');    -- 两位数日期
    formatted := REPLACE(formatted, '%e', 'FMDD');  -- 日期无前导零
    formatted := REPLACE(formatted, '%H', 'HH24');
    formatted := REPLACE(formatted, '%h', 'HH12');
    formatted := REPLACE(formatted, '%i', 'MI');
    formatted := REPLACE(formatted, '%S', 'SS');
    formatted := REPLACE(formatted, '%s', 'SS');
    formatted := REPLACE(formatted, '%p', 'AM');
    formatted := REPLACE(formatted, '%W', 'FMDay');
    formatted := REPLACE(formatted, '%a', 'FMDy');
    formatted := REPLACE(formatted, '%b', 'FMMon');
    formatted := REPLACE(formatted, '%M', 'FMMonth');
    formatted := REPLACE(formatted, '%D', 'FMDDth'); -- 带后缀的日期
    formatted := REPLACE(formatted, '%w', 'D');
    formatted := REPLACE(formatted, '%f', 'US');
    
    -- 恢复字面量 %
    formatted := REPLACE(formatted, '%%TEMP%%', '%');
    
    RETURN to_char(date_val, formatted);
END;
$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

-- 创建兼容 MySQL DATE_SUB(date, INTERVAL expr unit) 的函数
-- 支持语法：date_sub(日期, '数值 单位')，如 date_sub(NOW(), '6 DAY')
CREATE OR REPLACE FUNCTION date_sub(
    date_val TIMESTAMPTZ,  -- 输入日期（支持带时区的时间戳）
    interval_str TEXT      -- 间隔字符串（如 '6 DAY'、'1 HOUR'、'30 MINUTE'）
) RETURNS TIMESTAMPTZ AS $  -- 返回减去间隔后的日期
BEGIN
    -- PostgreSQL 中直接用 "日期 - INTERVAL '间隔字符串'" 实现减法
    -- 这里将输入的间隔字符串转换为 PG 的 INTERVAL 类型
    RETURN date_val - (interval_str || 's')::INTERVAL;
    -- 注：拼接's'是为了兼容 MySQL 可能的单数单位（如 DAY 而非 DAYS），PG 同时支持单复数
END;
$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

-- 创建兼容 MySQL DATE_ADD(date, INTERVAL expr unit) 的函数
-- 支持语法：date_add(日期, '数值 单位')，如 date_add(NOW(), '6 DAY')
CREATE OR REPLACE FUNCTION date_add(
    date_val TIMESTAMPTZ,  -- 输入日期（支持带时区的时间戳）
    interval_str TEXT      -- 间隔字符串（如 '6 DAY'、'1 HOUR'、'30 MINUTE'）
) RETURNS TIMESTAMPTZ AS $  -- 返回加上间隔后的日期
BEGIN
    -- PostgreSQL 中用 "日期 + INTERVAL '间隔字符串'" 实现加法
    -- 拼接's'兼容 MySQL 单数单位（如 DAY），PG 同时支持单复数
    RETURN date_val + (interval_str || 's')::INTERVAL;
END;
$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

-- 创建兼容 MySQL CURDATE() 的函数
-- 功能：返回当前系统日期（仅包含年月日，不带时间）
CREATE OR REPLACE FUNCTION CURDATE()
RETURNS DATE AS $  -- 返回类型为 DATE（与 MySQL 一致）
BEGIN
    -- PostgreSQL 中 CURRENT_DATE 直接返回当前日期（date类型），与 MySQL CURDATE() 行为完全一致
    RETURN CURRENT_DATE;
END;
$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

还有TIME_TO_SEC UNIX_TIMESTAMP FROM_UNIXTIME，之类的我也是让豆包给我写对应的兼容函数，减少代码修改工作量。

-- 函数1：处理不带时区的 TIME 类型（与date_sub的明确类型参数风格一致）
CREATE OR REPLACE FUNCTION time_to_sec(
    time_val TIME  -- 明确接收不带时区的时间
) RETURNS INTEGER AS $
BEGIN
    RETURN EXTRACT(EPOCH FROM time_val)::INTEGER;
END;
$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

-- 函数2：处理带时区的 time with time zone 类型（重载，保持函数名一致）
CREATE OR REPLACE FUNCTION time_to_sec(
    time_val TIMETZ  -- 明确接收带时区的时间
) RETURNS INTEGER AS $
BEGIN
    -- 先转换为不带时区的本地时间，再提取秒数（与MySQL逻辑一致）
    RETURN EXTRACT(EPOCH FROM (time_val::TIME))::INTEGER;
END;
$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

Limit语法替换

mysql和pg都支持limit取最前面n行数据，但如果是需要跳过offset行，取n行数据，两者语法不一样。mysql是使用LIMIT offset, n 跳过 offset 行取 n 行；PostgreSQL 需显式写 LIMIT n OFFSET offset，不支持逗号分隔的简写形式。需要用idea正则找到相关语句，逐个修改。（如果是mybatis plus生成的分页语句则不需要修改，会自动适配）

sql里面有变量的语法替换

mysql支持在查询中使用@定义和使用变量，pg不支持。好在这种sql不多，全部找出来后让豆包改写成同义的postgresql查询就可以。

if系列函数替换

ifnull isnull if等这些函数，pg是没有的，需要替换。为了减少迁移工作量，如果有相同语义函数，如ifnull（可用COALESCE代替），则用对应函数代替，如果没有则自己创建一个。

-- 创建支持嵌套调用的pg_if函数
CREATE OR REPLACE FUNCTION pg_if(
    condition BOOLEAN,
    value_if_true ANYELEMENT,
    value_if_false ANYELEMENT
) RETURNS ANYELEMENT AS $
BEGIN
    -- 使用PG 17原生CASE表达式实现条件判断，支持任意层级嵌套
    RETURN CASE WHEN condition THEN value_if_true ELSE value_if_false END;
END;
$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

-- 专门解决：pg_if(boolean, integer, smallint) 类型组合的调用
CREATE OR REPLACE FUNCTION pg_if(
    condition BOOLEAN,               -- 第一个参数：布尔条件
    value_if_true INTEGER,           -- 第二个参数：integer（如 0）
    value_if_false SMALLINT          -- 第三个参数：smallint（如 device_alarm.alarm_type）
) RETURNS SMALLINT AS $  -- 返回值类型与第三个参数一致（smallint）
BEGIN
    -- 将 integer 类型的 value_if_true 转为 smallint，确保类型兼容
    RETURN CASE WHEN condition THEN value_if_true::SMALLINT ELSE value_if_false END;
END;
$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

这里特别介绍一下查找if函数的方法，因为mybatis语法的xml代码里面本身有很多if，如果直接使用if关键字查，会把if test这种也找出来。

这里可以使用正则语法中的负向预查（Negative Lookahead）语法，找出包含if但不包含test的代码行：

^(?=.*if)(?!.*test).*$

null比较语法修改

mysql支持= null，在pg统一需要修改为is null

mysql系统表替换

有些代码需要查询mysql的information_schema系列表，需要用豆包改成相同语义的函数（注意告诉豆包你的pg版本，免得生成了错的）。

下面是我让豆包写的，查询数据库空间占用的sql

SELECT
        ROUND(SUM(pg_total_relation_size(relid)) / 1024.00 / 1024.00, 2) AS msSize
        FROM
        pg_stat_user_tables
        WHERE
        schemaname = 'public'

双引号字符串

mysql支持单引号字符串，也支持双引号字符串，但pg只支持单引号字符串，双引号字符串会被认为是标志符。需要批量换出来替换。

主键自增

MySQL 支持主键自动自增（基于表中现有最大值递增）；PostgreSQL 虽支持主键自增，但不会自动基于表中现有最大值续增。我统一改成使用mybatis plus的雪花id生成功能替换自增功能。

MySQL 的主键自增会自动基于表中现有最大值续增；PostgreSQL 虽支持主键自增，但依赖独立序列管理，不会自动同步表中现有最大值。为了减少后续数据迁移工作，我把所有id统一改成使用mybatis plus的雪花id生成。

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 全局ID生成策略(如果没额外配置则使用雪花算法)
  global-config:
    db-config:
      id-type: assign_id

只要表的字段名是id或者有用@TableId注解（注意IdType不能手动指定为AUTO），mybatis plus插入数据时就会自动生成id。

不过有一些表的数据登记是直接在mybatis的xml里面写sql的，这种就需要手动改代码，提前在外面生成好id传进来。

由于雪花算法生成的id比较长，相应的java代码也要把id字段改成Long。

另外需要注意，有些代码使用了LAST_INSERT_ID保留id，改成mybatis plus后代码逻辑要进行相应调整。

@SelectKey(databaseId = "mysql", statement = "SELECT LAST_INSERT_ID() AS id", keyProperty = "id", before = false, resultType = Integer.class)

Value关键字改为Values

这个简单替换就行，pg不支持value，所有insert都要使用values

REPLACE和ON DUPLICATE KEY UPDATE语法

需要改为ON CONFLICTE ... DO NOTHING或者DO UPDATE

需要注意改了后低版本mybatis plus的多租户插件可能会报错，报jsqlparser语法解析失败。

网上说要把jsqlparser升级到4.5以上，但因为我改的sql都不需要考虑多租户，所以只是简单的用@InterceptorIgnore(tenantLine = "true")，让插件忽略。

Update关联更新语法修改

MySQL支持的UPDATE ... JOIN是 MySQL 自己的语法扩展，在需要PostgreSQL 使用 UPDATE ... FROM 关联更新语法。

其它

完成以上工作后，系统一般就能运行了。接下来就是一些没办法批量替换的工作。

PostgreSQL语法限制比较严格，例如：

1. 查询中未被聚合函数处理的字段，必须包含在 GROUP BY 子句中
2. 字符串、数字、日期之间不能直接比较或者进行表关联，需显式转换类型（如 ::timestamp 或 ::varchar）

这种只能跑到哪里报错了，就改哪里。改完计算让豆包先审核一下，避免改错。

剩余工作

改完后系统基本可以运行，不过还有一些工作要做，这些我还在改，如果后面发现有值得分享的，再补充。

1 navicat迁移时，把json迁移成text了，其实是要改成jsonb更合适。而且相应的json操作函数也可看看有没有不兼容的。

2 quartz等中间件配置兼容postgresql。

3 mysql特有的ON UPDATE CURRENT_TIMESTAMP语法，需要使用mybati的自动填充功能代替，在update_time字段统一加上@TableField(fill = FieldFill.INSERT_UPDATE)注解。

程序员的进阶路上，经验从来不是负担。
关注我，跟你分享一个资深物联网应用开发者的见闻。

点击关注，不错过更多实战干货