从Mariadb迁移到postgresql

发表于 更新于 分类于 ， ， ， 阅读次数： 本文字数： 2k 阅读时长 ≈ 2 分钟

1. 前言

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

mysql driver:

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

迁移后PostgreSQL driver:

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

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

1
2
3

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

2. 两者之间的差异

  1. 连接字符串不一样

    mariadb:

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

    打开mysql连接:

    1
    sql.Open("mysql", dataSourceName)

    postgresql:

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

    打开PostgreSQL连接:

    1
    sql.Open("pgx", dataSourceName)

  2. sql参数语法不一样

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

  3. 一些函数不一样

    函数MySQL等价的PostgreSQL函数
    随机函数Rand()Random()
    当前日期curdate()current_date
    当前时间without timezoneCURTIME()LOCALTIMESTAMP
    日期的加减curdate() + INTERVAL 1 DAYcurrent_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不会。

    1
    2
    3
    4
    5
    6
    7
    8
    -- 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