从Mariadb迁移到postgresql

1. 前言

本文主要实验所用的环境, 后端是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

2. 两者之间的差异

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 )

7. postgresql 字段名和表名大小写

   • PostgreSQL对表名、字段名都是区分大小写的。但是，PostgreSQL在SQL语句中对大小写是不敏感的，在不加双引号的情况下，创建时无论使用大写还是小写，表中都会统一转为小写显示的，因此查询时也会将语句中的字段名统一改成小写，因此，此时使用大小写查询均可。

   • 用SQL语句的时候需要加双引号，或者转义符号。如果要创建名称为大写字母的字段名，需加双引号，此时表中的字段名不含双引号，但是查询时需加上双引号，执行时语句中的双引号将被抹去，因此能查出对应字段。

   • 查询执行时，只会发生字段”双引号被抹去”和“大写转小写”中其中一个，优先发生”双引号被抹去”。

8. postgreSQL limit 语法

   postgreSQL 不支持 limit 10, 20 这种语法, 需要替换为 limit 20 offset 10