社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

项目从 MySQL 切换 PostgreSQL,踩了太多的坑!!!

鸭哥聊Java • 1 年前 • 401 次点击  

我们项目突然要从 MySQL 切 PostgreSQL。你别以为这事儿简单,真是...让我连续好几个晚上十二点才到家,吃饭都没心思,整个技术群里那几天都跟炸了锅一样。

那天是周三吧,在公司楼下小花园那儿抽烟,老板过来说,东哥你们的数据要跟大集团那边对接,人家主库用 Postgres,咱必须也跟上。我当时心里就咯噔一下,兄弟们都懂——老项目,ORM写死了,SQL全是 MySQL 语法,切库那就是大型灾难片啊。

然后回工位一看,项目里到处都是LIMIT ?, ?,各种ifnull,concat,自定义函数,MySQL的那一套玩意儿,换成Postgres全得改。你想想,sql 语法不一样,数据类型也对不上——本地调试一大堆报错,写测试脚本写到手抽筋。

数据类型,99%都能踩坑

印象最深的就是类型这块,MySQL 里 int、varchar、datetime 用得飞起,Postgres 这边 integer、text、timestamp。你一不留神,比如主键 id 字段,如果自动增长那种,MySQL 是auto_increment,Postgres 得serial,或者bigserial,结果一批脚本没改直接炸掉。

还有 boolean 字段,MySQL 你用 tinyint(1),Postgres 人家就直接 boolean 啦。前端传个 0/1,后端 insert 一下,不是 true 就是 false,搞得同事半夜打电话问我:“为啥我插进去的值都变成 t/f 啦?”

再有,时间戳,MySQL 的 datetime 默认可以空,Postgres timestamp 默认不让你空,迁移时候你表里有空值,直接 insert 失败,一行行对着数据库发呆,属实难受。

SQL语法,处处是坑

MySQL 写习惯了,像那种 limit 分页查询,肯定是LIMIT offset, size,Postgres 要写LIMIT size OFFSET offset,调了半天发现数据总不对。还有字符串拼接,MySQL 用concat(a, b),Postgres 得用a || b,一堆老代码全都要人肉翻译...

还有个奇葩事,之前有些同事写了很多find_in_set,Postgres 里根本没这玩意,得自己写position(','||str||',' in ','||set||',') > 0,或者用正则,一顿骚操作。最尴尬的还不是这些,是遇到类似 ifnull,Postgres 没这函数,人家叫coalesce,批量替换的时候总有漏网之鱼。

对了,还有事务和锁,MySQL 的隔离级别和默认行为和 Postgres 也不太一样,像 auto commit,悲观锁,死锁日志啥的都不一样。我们后端有个地方因为死锁直接挂死,查了两小时才定位出来。

JDBC 驱动和连接池也有骚操作

很多人只顾着改 SQL,忘了 spring boot 里的 JDBC 驱动要从mysql-connector-java换成postgresql,连接串也不一样了。MySQL 是jdbc:mysql://...,Postgres 得是jdbc:postgresql://...,还经常有参数没配全导致连接超时。还有 mybatis 里的 ,MySQL NULL 处理宽容,Postgres 严格得多。

代码里原本写着:

String sql = "SELECT * FROM user WHERE status = 1 LIMIT ?, ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, offset);
ps.setInt(2, pageSize);

现在得全部改成:

String sql = "SELECT * FROM user WHERE status = 1 LIMIT ? OFFSET ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, pageSize);
ps.setInt(2, offset);

稍微顺序一反,数据全错位。更坑的是有些第三方库不兼容,分页插件根本跑不通。

索引与表设计,泪流满面

MySQL 有那种全文索引fulltext,Postgres 直接上GIN、GiST,用法完全不一样。我们项目最早是like '%xxx%',直接全表扫,Postgres 直接建议你用 tsvector。你要真想迁移,索引和表结构全得重新设计。别的还有 enum、set 这些字段,MySQL 可以用,Postgres 根本不认,要么自己加 check 约束,要么干脆直接 text。

脚本迁移,大型翻车现场

还有一个很现实的问题——数据迁移。我们项目是用mysqldump导的,然后找了个第三方工具转成 Postgres 脚本,导进去之后各种编码问题,中文全成了乱码。然后外键约束导进去一堆报错,最后还是手动拆表,一个表一个表迁...

而且 MySQL 允许主键为空或者没有主键,Postgres 一上来各种不让过,像什么duplicate key value violates unique constraint,无主键表直接不给你插。那天和 DBA 对着库捣鼓到晚上十一点多。

再说代码,原来用的MySQLDialect,全部换成PostgreSQLDialect,Hibernate 里一大堆配置全得重新调。最坑的还是默认排序,MySQL 是大小写不敏感,Postgres 是大小写敏感,排序全乱了。后来只能每个字段都加lower()函数。

连 group by都有不同,MySQL 你可以只 group by id,select 列里啥都能放,Postgres 直接不让你过,非聚合的字段必须都 group by...有时候报个错你都想摔鼠标。

其实最大的问题,不是 SQL 改起来有多难,是你永远低估了代码里的小细节。迁移过程中,我推荐一定要先把单元测试覆盖全了,然后找工具全量扫描一遍所有用到 MySQL 语法的地方,别偷懒,能自动化的自动化。

如果让我再来一次...真不如当时直接双写两套 DAO,慢慢迁移,别想着一步到位。坑太多,真不是开玩笑。

-END-

我为大家打造了一份RPA教程,完全免费: https://www.songshuhezi.com/rpa.html

🔥鸭哥私藏精品 🔥

鸭哥作为一名老码农,整理了全网最全《Java高级架构师资料合集》。总量高达 650GB  。点击下方公众号回复关键字 Java 全部免费领取

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/184624