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

Myth丶恋晨 2023-09-24 23:39 43阅读 0赞

## 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存在，为了兼容两者，我们在创建表时使用的类型一定需要具有通用性，下面总结了一份两者兼容类型表格提供参考：

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

### 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`