preface

The company recently switched from Mysql to CockroachDB, so some problems encountered during this period are recorded here

1 development environment

CockroachDB version V20.2.3

maven needs to introduce dependencies

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

Profile modification

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

Note that if & sslmode = require is not added, the following error will be reported

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

2 database table migration

For database migration, I use Navicat

1. First open the mysql table and reverse the database to the model
2. Save model as PostgreSQL
3. Then export the sql script
4. Handle fields and incompatible symbols. At this time, sql scripts cannot be used directly. For example, the symbol needs to be removed, and then the data type can be replaced globally according to the official document
5. Create a new PG database and execute sql

3 self increasing id

Because CockroachDB is a distributed database, the official recommendation is to use UUID as the primary key, but most of the primary keys of our company's projects are long, so I use SERIAL8 as the primary key, which is the bigint in mysql.

The SERIAL type is equivalent to INT DEFAULT unique_rowid().

This means that the int type is unique and self increasing by default. If SERIAL is not used, unique must be added later_ ROWID () is the only self increasing function.

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

The generated primary key id is unique and self increasing, but not necessarily continuous. The official explanation is to generate id faster. If you want the insert statement to return id, you also need to add the RETURNING PRIMARY KEY at the end of the sql statement.

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

3 paging query

The paging of CockroachDB is also a little different from mysql.

// mysql
SELECT * FROM accounts limit pageBegin pageSize;

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

Offset indicates where the offset starts, that is, the query starts from the first data.

3 DATE_FORMAT

Format time function replacement

// 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;

Note that the following strftime notation is not the same.

3 GROUP_CONCAT

CockroachDB does not have a GROUP_CONCAT is a function, but we can use the function array_ Instead of AGG, if you want to return a spliced string type, you can also wrap another layer of function array outside_ to_ string

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

Indicates that a type is returned_ An array of ID and concatenated with, numbers.

3 JSONB

The JSONB type used here is mainly to replace the data type previously stored in mongoDB. Because our company's business has only two fields, one id and one json content, the update statement of CockroachDB is directly used here, which is equivalent to the ON DUPLICATE KEY UPDATE of MySQL. I think it is too convenient to use here than mysql.

Official introduction

UPSERT Statement is INSERT ON CONFLICT Short for statement. When UPSERT When the specified value of does not violate the uniqueness constraint, insert the data. If the uniqueness constraint is violated, update this row of data

Simply put, if your primary key is the uniqueness constraint, just use UPSERT. Otherwise, you need to use INSERT ON CONFLICT.

Insert statement

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

reference material

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