CockroachDB 整合Springboot的简单应用

前言

公司最近从 Mysql 切换到了 CockroachDB ，所以在这里记录下期间遇到的一些问题

1 开发环境

CockroachDB版本 V20.2.3

maven需要引入依赖

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.9</version>
</dependency>

配置文件修改

spring.datasource.url=jdbc:postgresql://host:port/DB?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&sslmode=require

spring.datasource.driver-class-name=org.postgresql.Driver

这里需要注意如果没有加上 &sslmode=require 会报下面的错误

Caused by: org.postgresql.util.PSQLException: ERROR: node is running secure mode, SSL connection required

2 数据库表迁移

数据库迁移的话我用的是Navicat

1. 先打开mysql的表逆向数据库到模型
2. 另存模型为PostgreSQL
3. 然后导出sql脚本
4. 处理字段和不兼容符号，这个时候sql脚本还不能直接使用，需要处理一些比如这个 ` 符号要去除，然后数据类型可以对照官方文档进行全局替换
5. 新建PG数据库执行sql

3 自增id

CockroachDB 因为是分布式数据库的原因，官方推荐是使用UUID来当主键，但是我们公司项目大部分主键是long类型的，所以我使用了 SERIAL8 类型用作主键，SERIAL8 就是mysql里面的bigint。

SERIAL 类型等效于 INT DEFAULT unique_rowid()。

意思就是int类型默认是唯一自增的，如果不用SERIAL 后面就要加入 unique_rowid()这个唯一自增的函数。

CREATE TABLE logon (
    user_id SERIAL primary key NOT NULL,
    logon_date DATE
);

现在生成的主键id就是唯一且自增的，但是不一定连续,官方解释是为了更快的生成id。如果要让insert语句返回id还需要在 sql语句最后加上 RETURNING PRIMARY KEY。

INSERT INTO users(username) VALUES ('jack') RETURNING user_id;

3 分页查询

CockroachDB 的分页跟mysql也有一点不一样。

// mysql
SELECT * FROM accounts limit pageBegin pageSize;

// CockroachDB
SELECT * FROM accounts limit pageSize offset pageBegin;

offset 表示偏移从哪里开始，也就是从第几条数据开始查询。

3 DATE_FORMAT

格式化时间函数替换

// mysql
SELECT
    DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s'),
FROM
    talbe;

// CockroachDB
SELECT
    experimental_strftime(create_time, '%Y-%m-%d-%H-%M-%S'),
FROM
    talbe;

注意后面的strftime表示法并非一样。

3 GROUP_CONCAT

CockroachDB 并没有 GROUP_CONCAT 这个函数，不过我们可以使用 函数 array_agg 替代，如果要返回一个拼接的string类型，还可以在外面再包一层函数 array_to_string

SELECT
    array_to_string(array_agg(type_id),','),
FROM
    talbe

表示返回一个 type_id的数组并用,号进行拼接。

3 JSONB

这里使用到 JSONB 类型主要是替换之前存储再mongoDB里面的数据类型，因为我们公司业务就只有两个字段，一个id，一个json内容，所以这里直接用 CockroachDB 的 UPSERT 语句，相当于mysql 的 ON DUPLICATE KEY UPDATE ，我觉得这里使用起来比mysql方便太多。

官方给出的介绍

UPSERT 语句是 INSERT ON CONFLICT语句的简称。当UPSERT的指定值不违反唯一性约束时，插入数据，如果违反了唯一性约束，则更新这行数据

简单来说 如果你的主键就是唯一性约束条件就用 UPSERT 就行了，否者需要用 INSERT ON CONFLICT。

插入语句

UPSERT INTO user_template(template_id ,user_template) values(1,'json')

参考资料

http://doc.cockroachchina.baidu.com/#develop/sql-feature-support/sql-support-summary/