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 ='任务表';
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'
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。
第二次插入,有冲突,但修改的列值和表中值不同,会返回主键ID,RowAffected=2。
第三次插入,有冲突,但修改的列值和表中值相同,不会返回主键ID,RowAffected=0.
不管第一次插入还是冲突修改,都会返回主键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:
当然如果使用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…