从Mariadb迁移到postgresql

前言

本文主要实验所用的环境, 后端是golang, gin web框架.

mysql driver:

github.com/go-sql-driver/mysql v1.4.1

迁移后PostgreSQL driver:

github.com/jackc/pgx/v5 v5.1.1

迁移前服务器版本为:
mariadb 版本为5.5.68-MariaDB

mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

两者之间的差异

1. 连接字符串不一样

   mariadb:

   # dataSourceName
   username:******@TCP(localhost:3306)/test?charset=utf8mb4
   

   打开mysql连接:

   sql.Open("mysql", dataSourceName)
   

   postgresql:

   # dataSourceName
   postgres://eagle:****@localhost:5432/test
   

   打开PostgreSQL连接:

   sql.Open("pgx", dataSourceName)
   

2. sql参数语法不一样

   mysql的sql参数占位符是? 而PostgreSQL的sql参数占位符是$1,$2,$3.....

3. 一些函数不一样

   函数	MySQL	等价的PostgreSQL函数
   随机函数	Rand()	Random()
   当前日期	curdate()	current_date
   当前时间without timezone	CURTIME()	LOCALTIMESTAMP
   日期的加减	curdate() + INTERVAL 1 DAY	current_date + INTERVAL '1 DAY'
   空值替换	IFNULL()	COALESCE()
   日期格式化	DATE_FORMAT(MEMO_DATE,"%Y%m%d")	TO_CHAR(CURRENT_DATE, 'yyyyMMDD')

4. 对bool值的处理不同

   PostgreSQL对数值处理更加严谨

   mysql 可以将整数0 作为bool值false插入数据库, 而postgresql不行, 必须为bool值FALSE 或FALSE才能被作为bool值.

5. 处理Blob类型数据的方式不一样

   PostgreSQL有两种Blob类型, 一种是OID(Object identifiers), 一种是bytea, 而Bytea类型更接近Mysql数据库中的BLOB. OID字段 只保存了对象的4位的identifier(an unsigned four-byte integer), 具体内容存储在Postgresql的Large_object内部表中, 用户只能通过Postgresql提供的函数或者驱动提供的API才能读取, 通过identifier获取具体的内容. 当然这样的设计有它的好处, 即不用在查询表时将大块的数据加载到内存, 而是在实际需要时再通过identifier去取回数据. 既能保证数据的完整性, 又能获得将数据保存在数据库外部同样的优势,同时能获得数据库提供的操纵binary data的函数, 无疑是一种比较先进的理念.

   比较无奈database/sql也就是标准库, 对这种模式支持不够好, 目前没有提供标准的操作类似于PostgreSQL OID的接口, 要使用OID类型的数据, 还必须依赖与具体的驱动程序.

6. insert 语句

   Mysql 支持“insert into” 和 "insert", 而postgreSQL只支持"insert into"， 例如以下语句在postgreSQl会报语法错误， 而mysql不会。

       -- postgreSQl会报语法错误
       insert book_items(USER_ID, BOOK_ID, WORD_ID)
       values(1, 2, 100 )
   
       -- postgreSQl 中正确胡写法是
       insert into book_items(USER_ID, BOOK_ID, WORD_ID)
       values(1, 2, 100 )