这段时间对项目数据库进行了迁移（详见：java项目数据库从mysql迁移到postgresql），最近终于完成了。我整理了一下项目中常常出现的postgresql与mysql的语法差异，跟大家分享一下。

1 数据查询差异

总的来说，PostgreSQL对sql语法规范要求比较高，不像MySQL为了兼容性思考，有许多写得不怎么规范的sql也能正常使用。还有pg的类型比较有严格要求，大部分类型之前是不能隐式转换，所以也不支持直接进行类型比较。需要在sql里面强制指定类型转换后再比较。以下是一些细节差异：

1.1 pg不支持字符串与时间进行比较

MySQL时间与字符串是可以互转的，所以可以用时间字符与String做比较，但在PostgreSQL 不行，需要显示将字符串转换成时间类型才可以跟表里面的时间类型字段做比较。

如果是使用mybatis手写的比较sql，需要在字符串后面加上::timestamp，例如这样：“create_time <= concat(#{endTime}::text,' 23:59:59')::timestamp”

如果是使用mybatis plus，则需要使用Date或者LocalDate类型的变量做为绑定参数，例如：“queryWrapper.ge(“start_time”, DateUtil.toLocalDateTime(startDate))”

同样的， MySQL支持用start_time like concat(#{startTime},'%') 查询一天的数据，pg不支持，需要改为标准的between

1.2 pg不支持不规范的group by

在 PostgreSQL 中，SELECT 子句中出现的字段，必须要么包含在 GROUP BY 子句中，要么通过聚合函数（如 SUM()、AVG() 等）处理；如果某个字段既不在 GROUP BY 中，也没有用聚合函数处理，PostgreSQL 会直接报错，严格遵循 SQL 标准。

而 MySQL 在默认配置下（如启用 ONLY_FULL_GROUP_BY 关闭时）允许这种 “不规范” 写法，即 SELECT 中可以包含未在 GROUP BY 或未被聚合的字段（此时会随机返回分组中的某条记录值）。

此外，两者在 HAVING 子句对 count 的使用上也有差异：MySQL 支持简写 HAVING count > 0，而 PostgreSQL 要求必须明确聚合函数的参数，需写成 HAVING count(1) > 0（或 count(*) 等），不允许省略参数直接写 count。

1.3 limit语法有差异

标准的limt语法是LIMIT #{params.limit} OFFSET #{params.index}，但在MySQL可以简写为LIMIT #{params.index},#{params.limit}，这个在pg是不支持的，需要统一改为标准语法

1.4 user在pg是保留关键字，在MySQL不是

有一些用户表相关的查询，习惯使用as user给用户信息表取别名，都要改过来

1.5 pg默认对字符串比较是严格区分大小写的，而MySQL默认是不区分大小写的

例如这个sql：select 'abc' like 'ABC'，在pg查询返回是false，而在MySQL查询返回是true

1.6 MySQL支持字符串里面支持有转义符，pg默认不支持

举例来说，MySQL 支持的字符串如 ' 行一 ”
行二 '，插入数据库时会自动处理好转义符；而在 PostgreSQL 中，则需要写成 E' 行一”
行二 '，即在字符串前面加上E，代表里面有转义符，并且要把”改成”，由于PostgreSQL不支持这个转义。

1.7 MySQL对类型比较比较宽容，支持字符串与数字比较，但pg不支持

MySQL 默认对类型转换更 “宽容”，当字符串无法直接转为数字时，它会尝试 “强制转换”： 若字符串完全不含数字（如”ENABLE”），会被转为0； 若字符串以数字开头（如”123abc”），会取前面的数字部分（转为123）； 即使转换逻辑不合理，MySQL 也不会报错，而是返回转换后的结果（一般是0）

另外，用MySQL时，使用String id执行selectById不会报错，但在pg时，会提示类型错误。需要使用Long id才行。

同样，在使用jon关联表查询时，关联表字段需要是一样类型或者可以隐式转换的类型，如果不然，需要在某一边指定强制转换。

1.8 默认事务隔离级别的差异

PostgreSQL的默认隔离级别是Read Committed，而MySQL InnoDB是Repeatable Read，这个会导致事务中查询数据有一些细微差异。

1.9 pg不支持用””字符串

在Postgresql，””中间的内容是标识符，不是字符串，但在MySQL，可以用””和''来表明字符串

1.10 pg不支持as '列名'不支持

MySQL支持列别名前后加上''，Postgresql里面不支持

1.11 pg不支持在sql里面使用@定义变量

如果有需要写复杂的sql逻辑，提议使用存储过程取代

1.12 pg的json操作语法与mysql差异比较大

两个数据库对于json类型字段的操作函数不一样，而且在update修改json类型字段时，PostgreSQL需要显式使用::json或者::jsonb指定

1.13 pg不支持LAST_INSERT_ID函数

如果需要在插入后获取到id，提议在改为插入前就先将id生成，不要使用LAST_INSERT_ID，或者使用PostgreSQL的returning语法

2 数据修改差异

2.1 pg不支持ON UPDATE CURRENT_TIMESTAMP

如果某个时间字段需要在更新时自动更新，则需要mybatis plus的FieldFile功能进行更新。

@TableField(fill = FieldFill.INSERT_UPDATE)

注意： 这里只适用于使用mybatis plus生成的sql，如果是在xml里面自己写的sql，则需要手工把时间字段加进去更新。

配套的MetaObjectHandler组件代码：

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.Objects;

/**
 * 默认时间填充 取代原来MySQL的 ON UPDATE CURRENT_TIMESTAMP
 * <p>
 * 如果没有需要使用自己指定的MetaObjectHandler，需要把这个属性设置为false
 * @author Administrator
 */
@Component
@ConditionalOnProperty(
        name = "database.auto_update_filed",
        havingValue = "true",
        matchIfMissing = true
)
public class OnUpdateFieldHandler implements MetaObjectHandler {

    // 插入时填充：仅处理 fill 包含 INSERT 的字段
    @Override
    public void insertFill(MetaObject metaObject) {
        handleStrictFill(metaObject, "createTime", OperationType.INSERT);
        handleStrictFill(metaObject, "updateTime", OperationType.INSERT);
        handleStrictFill(metaObject, "updatedAt", OperationType.INSERT);
        handleStrictFill(metaObject, "operateTime", OperationType.INSERT);
        handleStrictFill(metaObject, "lastModifyTime", OperationType.INSERT);
    }

    // 更新时填充：仅处理 fill 包含 UPDATE 的字段
    @Override
    public void updateFill(MetaObject metaObject) {
        handleStrictFill(metaObject, "updateTime", OperationType.UPDATE);
        handleStrictFill(metaObject, "updatedAt", OperationType.UPDATE);
        handleStrictFill(metaObject, "operateTime", OperationType.UPDATE);
        handleStrictFill(metaObject, "lastModifyTime", OperationType.UPDATE);
    }

    // 操作类型：区分插入和更新
    private enum OperationType {
        INSERT, UPDATE
    }


    // -------------------------- 通用工具方法 --------------------------

    /**
     * 严格填充逻辑：仅当字段配置了匹配当前操作的 fill 策略时才填充
     * 效果等同于 MyBatis-Plus 的 strictInsertFill/strictUpdateFill
     */
    private void handleStrictFill(MetaObject metaObject, String fieldName, OperationType operationType) {
        // 1. 检查字段是否存在
        if (!metaObject.hasSetter(fieldName)) {
            return;
        }

        // 2. 获取字段上的 @TableField 注解，判断 fill 策略是否匹配当前操作
        if (!isFillStrategyMatch(metaObject, fieldName, operationType)) {
            return; // 策略不匹配，不填充
        }

        // 3. 检查字段是否未手动赋值（值为 null）
        if (Objects.nonNull(metaObject.getValue(fieldName))) {
            return; // 手动赋值优先，不覆盖
        }

        // 4. 根据字段类型填充当前时间（兼容Date和LocalDatetime）
        Class<?> fieldType = metaObject.getGetterType(fieldName);
        if (Date.class.equals(fieldType)) {
            metaObject.setValue(fieldName, new Date());
        } else if (LocalDateTime.class.equals(fieldType)) {
            metaObject.setValue(fieldName, LocalDateTime.now());
        }
    }

    /**
     * 校验字段的 @TableField(fill) 策略是否匹配当前操作类型
     * @return true = 策略匹配（需要填充），false = 策略不匹配（不需要填充）
     */
    private boolean isFillStrategyMatch(MetaObject metaObject, String fieldName, OperationType operationType) {
        try {
            // 获取实体类的 Class 对象
            Class<?> entityClass = metaObject.getOriginalObject().getClass();
            // 获取字段对象（思考父类字段，如 BaseDO 中的字段）
            Field field = getField(entityClass, fieldName);
            if (field == null) {
                return false; // 字段不存在（理论上不会走到这里，由于前面已通过 hasSetter 校验）
            }

            // 获取字段上的 @TableField 注解
            TableField tableField = field.getAnnotation(TableField.class);
            if (tableField == null) {
                return false; // 没有 @TableField 注解，默认不填充
            }

            // 获取注解中的 fill 策略
            FieldFill fill = tableField.fill();

            // 判断策略是否匹配当前操作类型
            if (operationType == OperationType.INSERT) {
                // 插入操作：策略为 INSERT 或 INSERT_UPDATE 时匹配
                return fill == FieldFill.INSERT || fill == FieldFill.INSERT_UPDATE;
            } else {
                // 更新操作：策略为 UPDATE 或 INSERT_UPDATE 时匹配
                return fill == FieldFill.UPDATE || fill == FieldFill.INSERT_UPDATE;
            }

        } catch (Exception e) {
            // 反射获取字段失败时，默认不填充
            return false;
        }
    }

    /**
     * 递归获取类或父类中的字段（支持继承关系，如 BaseDO 的字段）
     */
    private Field getField(Class<?> clazz, String fieldName) {
        try {
            return clazz.getDeclaredField(fieldName);
        } catch (NoSuchFieldException e) {
            // 当前类没有，检查父类
            Class<?> superClass = clazz.getSuperclass();
            if (superClass != null && superClass != Object.class) {
                return getField(superClass, fieldName);
            }
            return null;
        }
    }
}

2.2 pg不支持AUTO_INCREMENT

虽然 PG 也有通过序列（Sequence）实现的自增方案（如 serial、bigserial 或 IDENTITY 类型），但和 MySQL 不同 ——MySQL 每次自动生成的 id 都是表中最大 id+1，而 PG 的序列会维护独立计数器，不会主动同步表中 id 的实际最大值。因此，当手动插入比当前序列值更大的 id 后，后续通过序列自动生成的 id 会延续序列自身的计数（而非表中最大 id），可能小于表中已有的最大 id，最终引发主键冲突。 基于此，在 PG 表设计时提议： 优先将 id 字段设计为bigint类型（对应 Java 的Long），这样能更好适配 MyBatis-Plus 的自增逻辑，避免 Integer 的取值范围限制（Integer 最大值仅 2147483647，数据量大时易溢出）； 若业务必须用Integer类型，插入数据时需手动设置 id 为(SELECT COALESCE ( max( id ), 0 ) + 1 FROM tab)，但需注意：高并发场景下直接用该 SQL 会有重复 id 风险，提议搭配行锁（如select max(id) + 1 from tab for update）或其他防重逻辑。

ps：数据迁移时提议优先保留原类型（列如原 Integer 不强行改 Long）。我之前迁移时没思考这点，把大量 Integer 类型的 id 改成了 Long，不仅引发了一堆类型不匹配的编译报错，改了很久才解决；更隐蔽的是，部分实体类用BeanUtils.copyProperties拷贝时，Integer和Long类型不兼容，编译阶段不会报错，但运行时会直接忽略 id 字段的拷贝，导致数据插入 / 更新时 id 缺失，触发业务逻辑错误。

2.3 布尔类型字段插入/更新差异

核心差异：MySQL 对Boolean与字段类型的兼容性宽松，PostgreSQL 校验严格，易因类型 / 长度不匹配报错。 2.3.1 MySQL：支持隐式转换与自动截断 MySQL 中，int或varchar类型字段可直接配合 Java Boolean类型操作（插入 / 更新）： 若字段为int（如tinyint(1)）：自动将Boolean转为整数（true→1，false→0）； 若字段为varchar（含varchar(1)）：自动将Boolean转为字符串（true/false），超限会自动截断（如”true”截断为”t”），无报错。 2.3.2 PostgreSQL：严格校验，易触发长度错误 PostgreSQL 不支持隐式转换与自动截断，以表ucas_user_tenant的is_default字段（varchar(1)）为例： Java 实体isDefault为Boolean类型时，MyBatis 会将其转为”true”（长度 4）或”false”（长度 5）； 因字段仅支持长度 1，PostgreSQL 直接抛出错误：value too long for type character varying(1)。 提议 不要用Boolean类型，优先用Integer类型（0或1）。

2.4 update … set语法差异

MySQL支持update tab as t set t.col的语法，pg不支持set t.col的语法，要修改的字段前面不能有别名

2.5 UPDATE多表更新语法差异

核心差异：多表更新关联方式不同，PostgreSQL 不支持 MySQL 风格的直接拼接。

2.5.1 MySQL

多表更新支持两种简洁写法： 直接拼接表名：UPDATE t1, t2 SET t1.col=1 WHERE t1.id=t2.t1_id 用 JOIN 关联：UPDATE t1 JOIN t2 ON t1.id=t2.t1_id SET t1.col=1

2.5.2 PostgreSQL

必须通过 FROM 子句关联表，无 JOIN 写法： 语法固定：UPDATE t1 SET col=1 FROM t2 WHERE t1.id=t2.t1_id 关联表不能直接跟在 UPDATE 后。

pg不支持insert into… value

需要使用标准的insert into… values（即复数形式）

pg不支持delete后面加上order by

mysql虽然支持，但这种写法不标准，在PostgreSQL里面不支持这样写

pg不支持REPLACE和ON DUPLICATE KEY UPDATE语法

提议改成标准的merge into语法

隐式事务提交差异

— MySQL：某些 DDL 语句会隐式提交事务 START TRANSACTION; INSERT INTO table1 VALUES (1); ALTER TABLE table1 ADD COLUMN new_col INT; — 这里会提交！ INSERT INTO table2 VALUES (2); — 不在同一个事务中 ROLLBACK; — 不会回滚第一个 INSERT

其他

postgresql还支持超级多丰富的特性，例如更多索引类型，各式插件，全文索引等，有一些项目只用了postgresql就实现了redis、向量数据库、消息队列等中间件功能。