1. 背景

        在日常开发业务时可能会遇到这种问题：原本使用mysql数据库，由于迁移或某种原因，把数据源切换为postgresql，那么项目还能正常运作吗？其实在大部分场景下或许没有问题，但是在如数据结构、DDL/DML语法等有特殊差别的话，可能就无法兼容。因此，总结了一下mysql和postgresql上的差异，以及在gorm对于两个不同数据库中的兼容性问题。

本文不讲述两者的选型优劣，仅描述常见的基本差异，如基本类型、语法、DDL/DML、字符集、SDK以及平时使用时存在的问题做举例和总结，避免在业务改造兼容两者时花精力又踩坑。

为了方便书写，后续postgresql简称pg。

Mysql 5.7/pg 11为例

2. 两者差异

2.1 基本类型

        mysql与pg在基本数据类型中也是有些许差异。有些类型只有在mysql中存在，有些则是pg存在，为了兼容两者，我们在创建表时使用的类型一定需要具有通用性，下面总结了一份两者兼容类型表格提供参考：

类型	兼容类型	备注
数字类型	smallint/integer/bigint/decimal/serial	在mysql和pg中serial理解是不同的，在mysql中为bigint unsigned not null auto_increament unique缩写，在pg表示自增32位整数。另外pg使用bigserial建立主键，其内部转为sequence + nextval进行的
字符类型	char(n)/varchar(n)/text	在字符类型中pg支持较少，mysql支持较多如ENUM/MEDIUMTEXT等 mysql中text长度只有64KB，pg无限长另外mysql中varchar默认支持65535，但是表字符集如果是utf8mb4，那么varchar仅支持16,383长度。 postgresql理论上varchar可以存1G大小变长数据，如果需要更长，推荐使用text
二进制类型	blob	pg和mysql对于二进制类型定义分别为bytea和blob bytea：最大1G blob：最大64KB，mediumblob：8MB，longblob: 2G
日期类型	timestamp/date/time	暂无
布尔类型	boolean	mysql布尔默认存储类型是tinyint，两者在建表时都兼容boolean写法的。如果在pg中使用smallint，那么不兼容true和false，mysql则兼容。 另外需要注意的是，pg在boolean中无法用数字0和1表示false/true，只能用字符串"0"/"1"
json支持	json	pg对json支持较好，并且可以添加索引，拥有较好的查询性能。mysql则没有。json类型尽量还是不要使用，因为会出现兼容性问题，可用text代替

2.2 字符集

        在mysql5.7中，默认的字符集为latin1，所以在创建表时时要指定charset，否则对于中文以及部分符号支持不好。需要注意的是，charset=utf8在mysql5.7中为utf8mb3，这种字符集无法对表情等特殊符号支持，但是在mysql8.0之后默认字符集变为utf8mb4。​

在pg11中默认使用UTF-8字符集。

2.3 DDL

2.3.1 创建表

        mysql与pg在create语法中有许多不同点。mysql的建表语句通常无法在pg中执行，需要一定的改动，这里举一个例子:​

mysql：

CREATE TABLE IF NOT EXISTS `tasks`
(
    `id`               bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
    `project_name`     varchar(128) NOT NULL ,
    `scenario_id`      bigint UNSIGNED NOT NULL DEFAULT 0 ,
    `media_id`         bigint UNSIGNED NOT NULL DEFAULT 0 ,
    `report_id`        bigint UNSIGNED NOT NULL DEFAULT 0 ,
    `engine_run_id`    varchar(512) NOT NULL DEFAULT '' ,
    `task_name`        varchar(128) NOT NULL DEFAULT '' ,
    `status`           tinyint(3) NOT NULL DEFAULT '0',
    `rules`            mediumtext ,
    `created_time`     timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `updated_time`     timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `deleted`          tinyint UNSIGNED NOT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_project_name_task_name` (`project_name`, `task_name`),
    KEY                `idx_scenario_id` (`scenario_id`),
    KEY                `idx_created_time` (`created_time`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='任务表';

pg:

CREATE SEQUENCE tasks_seq;
CREATE TABLE tasks
(
    id               bigint       NOT NULL DEFAULT NEXTVAL('tasks_seq'),
    project_name     varchar(128) NOT NULL,
    scenario_id      bigint       NOT NULL DEFAULT 0,
    media_id         bigint       NOT NULL DEFAULT 0,
    report_id        bigint       NOT NULL DEFAULT 0,
    engine_run_id    varchar(512) NOT NULL DEFAULT '',
    task_name        varchar(128) NOT NULL DEFAULT '',
    status           smallint     NOT NULL DEFAULT 0,
    rules            text,
    created_time     timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_time     timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted          smallint     NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT uniq_project_name_task_name UNIQUE (project_name, task_name)
);
CREATE INDEX idx_tasks_scenario_id ON tasks (scenario_id);
CREATE INDEX idx_tasks_created_time ON tasks (created_time);

总结create语句常见不同：

类型​	mysql​	pg​
自增id​	mysql可通过serial或bigint UNSIGNED NOT NULL AUTO_INCREMENT加自增主键id​	pg可通过bigserial，或者上述方式创建自增主键id​
索引​	在创建表时指定任何索引类型，普通索引，唯一索引都可以​	仅可以指定唯一、主键索引，对于普通索引需要额外执行ddl语句创建​
文本类型​	有tinytext/text/mediumtext/longtext等不同长度类型，如果插入长度超过会被截断​	仅有text，可存储无限变长的字符串​​
字符串类型​	最多只能存25565字节数据​	大约能存1GB的数据​
注释​	直接在字段后方使用COMMENT即可添加​	对注释支持不友好，需要在表外执行COMMENT ON table/column [table_name] IS 'xxxx'来设置

另外需要注意的是，在pg相同库不同表建立相同名字的索引是不被允许的。

2.3.2 列操作

2.3.3 约束操作

2.3.4 索引操作

2.3.5 用户操作

2.3.6 小结

• 在表创建上，pg和mysql有许多不同。mysql可以在创建表时设置注释，设置索引等；pg则没有上述能力，只能通过再执行ddl语句进行。
• 在列操作上，正常情况下，新增列和删除列没有太大区别，唯一注意的是修改列的语法是不同的。​
• 在约束操作上，pg有额外的check约束，mysql则在8.0之后才有。​
• 在索引操作上，两者没过多区别。​
• 在用户操作上，两者语法不同。​
• pg在ALTER TABLE时可以添加IF EXISTS参数来判断只有表存在时才执行，MYSQL并没有。​
• 两者在用户权限控制上，pg显得更灵活，可以基于角色控制实现。

2.4 DML

2.4.1 插入数据

• 语法

        pg和mysql插入语句十分相似，省略列名时VALUES候需要跟表所有列值，也都支持批量插入。

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

• 插入时处理冲突的语法不同

        在mysql和pg，如果插入过程中发生唯一键冲突时，可以根据自己的需求修改该行的值（如时间），在这里mysql和pg是不同的，具体区别如下：​

mysql：

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...) ON DUPLICATE KEY UPDATE `name`='update'

pg:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); ON CONFLICT ("column1") DO UPDATE SET "column2"='xxx' RETURNING "column1"

可见mysql和pg在处理冲突上是不同的，另外pg可以指定冲突列，mysql则不行。

• 插入时数据类型自动转换的规则不同

        在MySQL中，如果插入的数据类型与表中的列不匹配，MySQL会自动将数据类型转换为适当的类型。例如，如果将一个字符串插入到整数列中，MySQL会将字符串转换为整数。这种自动转换可能会导致数据丢失或格式错误。

        在PostgreSQL中，转换没有mysql灵活，比如：INSERT INTO test(status,name) VALUES ('1.5', 'nihao3')插入整数类型status，在pg中无法插入，在mysql中会转为整数2插入。但是如果值为1.5浮点数，那么pg和mysql都可以插入并且自动转换。

        另外mysql的boolean类型实质上使用tinyint整数类型，所以在mysql中TRUE=1和FALSE=0，但是在pg中的boolean类型只能由TRUE ('1'/'yes'/'true'/'on')和FALSE('0'/'no'/'false'/'off')表示，数字0/1在pg中无法匹配boolean类型。

• 插入指定自增主键带来的问题

        在MySQL或pg中，在插入数据时是可以指定主键id显式插入，如INSERT INTO table_name (id, name, ...) VALUES (1, value2, ...);，这个在mysql中没什么问题，但是在pg中需要额外注意的是，如果显式插入后，其自增的开始序列并不会随之改变，所以下次不指定id插入可能发生(id=x)唯一键重复的错误。所以pg在插入数据就不要通过显式的方式插入。目前可以通过SELECT setval('tasks_seq', (SELECT max("id") FROM tasks))方式修改自增序列开始值。

2.4.2 更新数据​

• 语法

        mysql更新数据的语法兼容pg，两者一样。举个例子如下：

UPDATE table_name SET column1=value1, column2=value2 WHERE some_column=some_value;

• pg更新数据的扩展语法

        pg还支持FROM或RETURNING语法，分别表示从其他表中获取数据，以及返回被更新行的信息。​

① 将table1中column1的值设置为table2的value1。

UPDATE table1 SET column1 = table2.value1
FROM table2
WHERE table1.key = table2.key;

② 更新后返回所更新的行

UPDATE table_name SET column1=value1, column2=value2 WHERE some_column=some_value RETURNING id;

这里需要注意一点：mysql并没有提供RETURNING函数，所以在create后返回主键的操作其实是分插入+查询主键两步完成的。因此，如果使用gorm线程池时，在create时一定要加上事务，否则在高并发下会发生不可预计错误。

2.4.3 删除数据

• 语法

        mysql和pg删除的语法类似，如下：

DELETE FROM table_name WHERE some_column=some_value;

        但是pg除此之外还有扩展语法，DELETE同时使用RETURNING。下面例子表示，在删除列值为some_value后并返回删除的所有行。

DELETE FROM table_name WHERE some_column=some_value RETURNING *;

2.4.4 查询数据

        查询语句mysql和pg其实也是类似的，这里主要简单介绍下连接查询的区别。

mysql	pg
INNER JOIN(default)、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN	INNER JOIN(default)、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN

2.4.5 小结

• 正常使用的话，pg和mysql的dml语法其实十分接近，只有在数据类型转换上会有区别，
• 使用pg时，双引号代表的是列，单引号代表的是字符串；这和mysql不同，mysql反斜杠代表列，单引号和双引号都可以用作字符串。
• 在pg中数字0或1是无法代表boolean类型的，但是mysql可以。

3. SDK

3.1 gorm

        这里主要介绍gorm，兼容mysql和pg。

        gorm可以同时兼容mysql和pg，但是需要注意的是避免使用raw或者exec的语句，这样如果切换数据库将会可能发生兼容性问题，具体需要根据业务使用需要来看。下面我总结了一部分在使用gorm兼容mysql和pg上的一些可能出现的问题并附上解决方案。

温馨提醒：mysql和pg的dsn格式是不一样的。

1. 时间戳相关问题

• 时间戳默认值

        mysql和pg数据库都支持default CURRENT_TIMESTAMP，所以基本可以不需要考虑这个问题。不过gorm的tag上也可以设置default默认值，但是设置时需要考虑兼容性，否则会出错，如default:CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，该tag只可以用在mysql中，pg则无效。

解决方案：使用autoCreateTime或者default:CURRENT_TIMESTAMP都可以解决兼容性问题，gorm中推荐使用前者，既可以兼容int64类型也可以兼容time.Time类型。

• 更新数据时同时更新时间戳

        mysql和pg在数据更新上提供的方法不同，mysql可以使用on update方式更新时间列，但是pg不行，只能通过函数的方式实现。

解决方案：这里使用gorm提供的tag autoUpdateTime即可解决，如此在更新数据时，gorm会自动更新时间戳。

3.1.2 插入冲突问题

        日常业务有可能发生插入时冲突，此时根据业务需求可能需要更改指定列的值，mysql与pg由于在写法上不同，因此需要依赖gorm来完成。

解决方案：通过gorm的Clauses(clause.OnConflict{}).Create()解决。举个栗子：下面实现当uid冲突时更改name，此写法兼容mysql和pg。

err := MysqlGorm.Table("test").Clauses(clause.OnConflict{
   Columns: []clause.Column{{Name: "uid"}},
   DoUpdates: clause.Assignments(map[string]interface{}{
      "name": "update1",
   }),
}).Create(t2).Error

这了有坑：

mysql和pg通过分别Clauses(clause.OnConflict{}).Create()方式插入时，有些许不同。

mysql：

第一次插入，没有冲突时，会返回主键ID，RowAffected=1。

第二次插入，有冲突，但修改的列值和表中值不同，会返回主键ID，RowAffected=2。

第三次插入，有冲突，但修改的列值和表中值相同，不会返回主键ID，RowAffected=0.

pg：

不管第一次插入还是冲突修改，都会返回主键ID，这是因为pg的RETURNING语法，并且RowAffected=1。

所以，我们可以通过RowAffected来判断是否更新成功来确定是否返回主键ID。

3.1.3 连接查询问题

        mysql和pg在连接查询的语法上没有太大区别，但是需要注意的是，pg在连接查询时，对于其他表的条件设置，必须要加上双引号，否则会报错。下面举例：         gorm中使用join进行连接时，如果要求在where中设置条件，不能使用where("column = ?", x)方式，使用这个方式会导致上述兼容性问题。可以使用struct或map[string]interface{}方式。但是这两个方式仅支持=操作符，所以为了扩展，gorm推出了gormx解决这个问题。

3.1.4 时区问题

        使用gorm往pg插入时间在数据库显示的时间和本地时间是一致的，唯一的区别就是在查询的时候，pg查询出来的是UTC时区的当前时间，mysql则是+8时区的当前时间，但是如果不转换时区的话，使用起来其实没什么区别。具体如下图

mysql:

pg:

当然如果使用int64时间戳作为存储类型，就不需要考虑这些问题。

3.1.5 转义问题

        转义问题在mysql和pg都存在。gorm中如果使用?占用符来预编译字符'，gorm会默认将其转义为'。但是对于模糊查询，仅仅一个单引号转义是不够的，还有%、_、/这几个符号都需要进行转义，否则会发生一些问题。

解决方案：

• 使用gormx，这是一个专门支持查询，更新的gorm扩展工具。但需要注意，如果模糊查询文本中存在%还是需要自己手动转义，gorm不会转义%。

• 自己转义：

// escapeLike 函数将 SQL LIKE 子句中的保留字符进行转义
func escapeLike(str string) string {
   str = strings.ReplaceAll(str, "\", "\\")
   str = strings.ReplaceAll(str, "%", "\%")
   str = strings.ReplaceAll(str, "_", "\_")
   //str = strings.ReplaceAll(str, "'", "\'") // gorm里面已经将'进行转义了
   return str
}

4. 总结

        mysql和pg在各方面还是存在较多不同的地方，具体还是需要根据自己的业务需求去使用。总的来说各有优劣，pg在性能上可能不如mysql，但是pg安全性高，具有许多扩展能力（如支持并发创建索引等）。

        业务上如果需要兼容两者，那么在数据的定义、表结构的定义、结构体的定义等上面需要注意使用通用性更强的方式，否则很有可能会发生未知错误。

另外，目前有在做mysql创表语句转为其他数据库(目前只支持pg)语句的工具：github.com/sineycoder/… ，后面会不断完善对于ALTER/INSERT/DELETE等语句的支持。

5. 相关资料

• www.postgres.cn/docs/11/ind…
• dev.mysql.com/doc/refman/…
• gorm.io/
• developer.aliyun.com/article/688…