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>
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
- First open the mysql table and reverse the database to the model
- Save model as PostgreSQL
- Then export the sql script
- 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
- 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.
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.
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.
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.
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.
UPSERT INTO user_template(template_id ,user_template) values(1,'json')