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格局是不相同的。

时刻戳相关问题

时刻戳默许值

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。

第2次刺进，有抵触，但修正的列值和表中值不同，会回来主键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安全性高，具有许多扩展能力（如支撑并发创立索引等）。

事务上假如需求兼容两者，那么在数据的界说、表结构的界说、结构体的界说等上面需求留意运用通用性更强的办法，不然很有或许会产生未知过错。

5. 相关材料

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

mysql和postgresql差异？gorm如何同时兼容？

1. 背景

2. 两者差异

2.1 根本类型

2.2 字符集

2.3 DDL

2.3.1 创立表

2.3.2 列操作

2.3.3 束缚操作

2.3.4 索引操作

2.3.5 用户操作

2.3.6 小结

2.4 DML

2.4.1 刺进数据

2.4.2 更新数据

2.4.3 删去数据

2.4.4 查询数据

2.4.5 小结

3. SDK

3.1 gorm

时刻戳相关问题

3.1.2 刺进抵触问题

3.1.3 衔接查询问题

3.1.4 时区问题

3.1.5 转义问题

4. 总结

5. 相关材料

作者信息

mysql和postgresql差异？gorm如何同时兼容？

1. 背景

2. 两者差异

2.1 根本类型

2.2 字符集

2.3 DDL

2.3.1 创立表

2.3.2 列操作

2.3.3 束缚操作

2.3.4 索引操作

2.3.5 用户操作

2.3.6 小结

2.4 DML

2.4.1 刺进数据

2.4.2 更新数据​

2.4.3 删去数据

2.4.4 查询数据

2.4.5 小结

3. SDK

3.1 gorm

时刻戳相关问题

3.1.2 刺进抵触问题

3.1.3 衔接查询问题

3.1.4 时区问题

3.1.5 转义问题

4. 总结

5. 相关材料

相关文章

Socket学习网络基础

Spring JDBC模板及事务管理

Metal每日分享，波动滤镜/涂鸦滤镜效果

一起来聊聊 Go 的核心竞争力

作者信息

2.4.2 更新数据