解决 Spring Boot + MyBatis 项目迁移到 PostgreSQL 后的数据类型不匹配问题
解决 Spring Boot + MyBatis 项目迁移到 PostgreSQL 后的数据类型不匹配问题
项目场景
在当前的项目开发中，我们采用了 Spring Boot 作为后端开发框架，结合 MyBatis 进行数据库操作。起初，项目使用的是 MySQL 数据库，但随着业务的发展和对数据库功能的更高要求，我们决定将数据库迁移到 PostgreSQL。
Spring Boot 以其便捷的开发特性和强大的自动化配置能力，极大地提高了开发效率；MyBatis 则凭借其灵活的 SQL 映射和执行机制，让我们能够更精细地控制数据库操作。然而，当完成数据库从 MySQL 到 PostgreSQL 的迁移后，我们发现某些操作出现了异常，这主要是由于两种数据库在数据类型处理以及操作符的支持上存在差异，导致原有的 SQL 语句在 PostgreSQL 中无法正常执行。
问题描述
在项目中执行数据库查询时，抛出了如下异常：
org.springframework.jdbc.BadSqlGrammarException:
Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: boolean = integer建议：No operator matches the given name and argument types. You might need to add explicit type casts.位置：64
The error may exist in cn/iocoder/module/erp/dal/mysql/sale/ErpSaleOrderMapper.java (best guess)
The error may involve defaultParameterMap
The error occurred while setting parameters
从异常信息可以看出，原因是在 SQL 语句里用布尔类型的字段 deleted 和整数 0 进行比较，而 PostgreSQL 没有定义这样的操作符。在 Spring Boot + MyBatis 的项目结构中，这样的异常通常会影响到数据的查询和处理，进而导致业务逻辑无法正常执行。
原因分析
在 PostgreSQL 中，布尔类型（boolean）和整数类型（integer）是不同的数据类型，不能直接使用 = 操作符进行比较。deleted 字段一般是布尔类型，true 代表已删除，false 代表未删除，而在 SQL 中使用整数 0 与之比较，从而引发错误。这是因为 MySQL 和 PostgreSQL 对数据类型的处理方式不同，MySQL 在某些情况下可能会进行隐式类型转换，而 PostgreSQL 则更加严格，要求数据类型必须严格匹配。
解决方案
修改 SQL 查询语句
把整数 0 替换成布尔值 false，因为在 PostgreSQL 里，false 通常用于表示逻辑上的 “未删除” 状态。在 MyBatis 的映射文件中，对应的 SQL 语句可以修改为：
<select id="countNotDeletedOrders" resultType="java.lang.Integer">SELECT COUNT(*) AS total FROM erp_purchase_order t WHERE t.deleted = false
</select>
如果使用注解方式的 MyBatis 查询，Java 方法中的 SQL 语句可以修改为：
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;@Repository
public interface ErpPurchaseOrderMapper {@Select("SELECT COUNT(*) AS total FROM erp_purchase_order t WHERE t.deleted = false")Integer countNotDeletedOrders();
}
当然以上可能还是有问题那就简单粗暴
- 当试图将
boolean类型的数据直接转换为smallint（对应 SQL 里的int2）类型时，PostgreSQL 不支持这种直接的类型转换。可以采用先移除默认值，修改类型后再重新设置的方法，示例 SQL 代码如下：
-- 移除默认值
ALTER TABLE erp_purchase_order
ALTER COLUMN "deleted" DROP DEFAULT;-- 修改列的数据类型，使用 CASE 语句进行转换
ALTER TABLE erp_purchase_order
ALTER COLUMN "deleted" TYPE smallint
USING CASE WHEN "deleted" THEN 1 ELSE 0 END;-- 设置新的 smallint 类型的默认值
ALTER TABLE erp_purchase_order
ALTER COLUMN "deleted" SET DEFAULT 0;
注意事项
- 数据备份：在执行这些操作之前，务必备份数据库，以防数据丢失或损坏。由于 Spring Boot 项目的数据通常与业务紧密相关，数据丢失可能会导致严重的业务问题。
- 权限问题：要确保你有足够的权限来修改数据库中的表结构。在团队开发环境中，需要与数据库管理员协调好权限分配。
- 外键和约束：如果
deleted列涉及外键约束或其他约束，需要先处理这些约束，否则可能会导致操作失败。可以在删除列之前禁用相关约束，操作完成后再重新启用。 - 性能影响：对于大型表，删除和添加列以及更新数据可能会花费较长时间，并且会产生较大的事务日志，建议在低峰期进行操作，以免影响项目的正常运行。
通过以上方法，可以有效解决 Spring Boot + MyBatis 项目迁移到 PostgreSQL 后因数据类型不匹配而引发的问题。希望这些解决方案能对你有所帮助，让你的项目在新的数据库环境中稳定运行。