本文作者：仉良
个人主页：https://github.com/mindlesscloud

Apache DevLake 是一个研发数据平台，能够收集和整合各类研发工具的数据，比方 Jira、Github、Gitlab、Jenkins。

本文并不打算对数据库兼容这个问题做全面的总结，只是对咱们理论遇到的问题做一个记录，心愿能对有类似需要的人提供一个参考。

1、数据类型差别

PostgreSQL 不反对 uint 类型的数据类型

type JenkinsBuild struct {    common.NoPKModel    JobName           string  `gorm:"primaryKey;type:varchar(255)"`    Duration          float64 // build time    DisplayName       string  // "#7"    EstimatedDuration float64    Number            int64 `gorm:"primaryKey;type:INT(10) UNSIGNED NOT NULL"`    Result            string    Timestamp         int64     // start time    StartTime         time.Time // convered by timestamp    CommitSha         string}

其中的JenkinsBuild.Number字段的gorm struct tag 应用了UNSIGNED会导致建表失败，须要去掉。

MySQL 没有 bool 型

对于 model 里定义为 bool 型的字段，gorm 会把它映射成 MySQL 的 TINYINT 类型，在 SQL 里能够间接用 0 或者 1 查问，然而 PostgreSQL 里是有 bool 类型的，所以 gorm 会把它映射成 BOOL 类型，如果 SQL 里还是用的 0 或者 1 去查问就会报错。

以下是一个具体的例子（为了清晰起见咱们删掉了无关的字段），上面的查问语句在 MySQL 里是没有问题的，然而在 PostgreSQL 上就会报错。

type GitlabMergeRequestNote struct {    MergeRequestId  int    `gorm:"index"`    System          bool }    db.Where("merge_request_id = ? AND `system` = 0", gitlabMr.GitlabId).

语句改成这样后依然会有谬误，具体请见上面对于反引号的问题。

db.Where("merge_request_id = ? AND `system` = ?", gitlabMr.GitlabId, false)

2、行为差别

批量插入

如果应用了ON CONFLIT UPDATE ALL从句批量插入的时候，本批次如果有多条主键雷同的记录会导致 PostgreSQL 报错，MySQL 则不会。

字段类型 model 定义与 schema 不统一

例如在 model 定义中GithubPullRequest.AuthorId是 int 类型，然而数据库里这个字段是 VARCHAR 类型，插入数据的时候 MySQL 是容许的，PostgreSQL 则会报错。

type GithubPullRequest struct {    GithubId        int    `gorm:"primaryKey"`    RepoId          int    `gorm:"index"`    Number          int    `gorm:"index"`     State           string `gorm:"type:varchar(255)"`    Title           string `gorm:"type:varchar(255)"`    GithubCreatedAt time.Time    GithubUpdatedAt time.Time `gorm:"index"`    ClosedAt        *time.Time    // In order to get the following fields, we need to collect PRs individually from GitHub    Additions      int    Deletions      int    Comments       int    Commits        int    ReviewComments int    Merged         bool    MergedAt       *time.Time    Body           string    Type           string `gorm:"type:varchar(255)"`    Component      string `gorm:"type:varchar(255)"`    MergeCommitSha string `gorm:"type:varchar(40)"`    HeadRef        string `gorm:"type:varchar(255)"`    BaseRef        string `gorm:"type:varchar(255)"`    BaseCommitSha  string `gorm:"type:varchar(255)"`    HeadCommitSha  string `gorm:"type:varchar(255)"`    Url            string `gorm:"type:varchar(255)"`    AuthorName     string `gorm:"type:varchar(100)"`    AuthorId       int    common.NoPKModel}

3、MySQL 特有的函数

在一个简单查问中咱们已经应用了 GROUP_CONCAT 函数，尽管 PostgreSQL 中有性能相似的函数然而函数名不同，应用形式也有细微差别。

cursor2, err := db.Table("pull_requests pr1").        Joins("left join pull_requests pr2 on pr1.parent_pr_id = pr2.id").Group("pr1.parent_pr_id, pr2.created_date").Where("pr1.parent_pr_id != ''").        Joins("left join repos on pr2.base_repo_id = repos.id").        Order("pr2.created_date ASC").        Select(`pr2.key as parent_pr_key, pr1.parent_pr_id as parent_pr_id, GROUP_CONCAT(pr1.base_ref order by pr1.base_ref ASC) as cherrypick_base_branches,             GROUP_CONCAT(pr1.key order by pr1.base_ref ASC) as cherrypick_pr_keys, repos.name as repo_name,             concat(repos.url, '/pull/', pr2.key) as parent_pr_url`).Rows()

解决方案：
咱们最终决定把 GROUP_CONCAT 函数的性能拆分成两步，先用最简略的 SQL 查问失去排序好的多条数据，而后用代码做聚合。

批改后：

cursor2, err := db.Raw(        `            SELECT pr2.pull_request_key                 AS parent_pr_key,                   pr1.parent_pr_id                     AS parent_pr_id,                   pr1.base_ref                         AS cherrypick_base_branch,                   pr1.pull_request_key                 AS cherrypick_pr_key,                   repos.NAME                           AS repo_name,                   Concat(repos.url, '/pull/', pr2.pull_request_key) AS parent_pr_url,                    pr2.created_date            FROM   pull_requests pr1                   LEFT JOIN pull_requests pr2                          ON pr1.parent_pr_id = pr2.id                   LEFT JOIN repos                          ON pr2.base_repo_id = repos.id            WHERE  pr1.parent_pr_id != ''            ORDER  BY pr1.parent_pr_id,                      pr2.created_date,                      pr1.base_ref ASC            `).Rows()

4、语法差别

反引号

某些 SQL 语句中咱们应用了反引号，用来爱护字段名，免得跟 MySQL 保留字有抵触，这种做法在 PostgreSQL 会导致语法错误。为了解决这个问题咱们从新扫视了咱们的代码，把所有跟保留字抵触的字段名做了批改，同时去掉了 SQL 语句中的反引号。例如方才提到的这个例子：

db.Where("merge_request_id = ? AND `system` = ?", gitlabMr.GitlabId, false)

解决方案：咱们把system改个名字is_system，这样就能够把反引号去掉。

db.Where("merge_request_id = ? AND is_system = ?", gitlabMr.GitlabId, false)

不标准的删除语句

咱们的代码中已经呈现过这种删除语句，这在 MySQL 中是非法的，然而在 PostgreSQL 中会报语法错误。

err := db.Exec(`    DELETE ic    FROM jira_issue_commits ic    LEFT JOIN jira_board_issues bi ON (bi.source_id = ic.source_id AND bi.issue_id = ic.issue_id)    WHERE ic.source_id = ? AND bi.board_id = ?    `, sourceId, boardId).Error

理解更多最新动静

官网：https://devlake.incubator.apache.org/

GitHub：https://github.com/apache/incubator-devlake/

Slack：通过 Slack 分割咱们