Unique primary key ID of tidb sub table -- solution to failure of sequence and Gorm to obtain primary key

It's reasonable to use TiDB After that, you don't have to consider the problem of sub database and sub table , Business scenarios should also be supported MySQL, And there is indeed some isolation in the data , Therefore, the horizontal table is divided by data type .

MySQL The primary key after horizontal table splitting id adopt snowflake To write , and TiDB Primary key of id Decide to pass tidb Of sequenceID To achieve

Sequence

Sequence It is the number sequence that the database system increases itself according to certain rules , Having unique and monotonically increasing properties . In the official SQL 2003 In the standard , It is defined as " A mechanism for generating continuous values ,Sequence It can be either an internal object , It can also be an external object ". Because native MySQL Not supported in Sequence, therefore TiDB Sequence The grammar of MariaDB、Oracle and IBM Db2

• Create Sequence grammar

CREATE [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name 
[ INCREMENT [ BY | = ] INCREMENT ] 
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ] 
[ MAXALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ] 
[ START [ WITH | = ] start ] 
[ CACHE [=] cache | NOCACHE | NO CACHE] 
[ CYCLE | NOCYCLE | NO CYCLE] 
[ ORDER | NOORDER | NO ORDER] 
[table_options]

• Show Create Sequence grammar

SHOW CREATE SEQUENCE sequence_name

• Drop Sequence

DROP [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name

• Get the next value

SELECT NEXT VALUE FOR sequence_name; 
SELECT NEXTVAL(sequence_name);

Example

Create three tables with the same structure , The table is divided into tables according to business types

CREATE SEQUENCE seq_for_autoid START WITH 1 INCREMENT BY 1 CACHE 1000 NOCYCLE;

SHOW CREATE SEQUENCE seq_for_autoid;

CREATE TABLE `user_1` ( 
 	`autoid` int(11) DEFAULT nextval(seq_for_autoid),
  `userid` varchar(32) NOT NULL,  
 	PRIMARY KEY (`autoid`) 
);
 
CREATE TABLE user_2 LIKE user_1;
CREATE TABLE user_3 LIKE user_1;

• Insert the test

INSERT INTO user_1(userid) VALUES ('a1'),('b1'),('c1'),('d1'),('e1'),('f1');
INSERT INTO user_2(userid) VALUES ('a1'),('b1'),('c1'),('d1'),('e1'),('f1');
INSERT INTO user_3(userid) VALUES ('a1'),('b1'),('c1'),('d1'),('e1'),('f1');

user_1 surface ：

user_2 surface ：

user_3 surface ：

gorm Batch insert interface for , Unable to return the obtained primary key value

import (
	"github.com/sirupsen/logrus"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

type User_1 struct {
    
	AutoId int    `gorm:"column:autoid;primarykey"`
	UserId string `gorm:"column:userid"`
}

func main() {
    
	db, err := gorm.Open(mysql.Open("test:test@tcp(127.0.0.1:4000)/apiserver?charset=utf8mb4&parseTime=true&loc=Local")})
	if err != nil {
    
		return
	}
	var users []User_1
	users = append(users, User_1{
    UserId: "6"})
	users = append(users, User_1{
    UserId: "7"})
	users = append(users, User_1{
    UserId: "8"})
	users = append(users, User_1{
    UserId: "9"})
	users = append(users, User_1{
    UserId: "10"})
	db.Debug().Create(&users)
	logrus.Info(users)
}

// Running results 
[14.247ms] [rows:5] INSERT INTO `user_1` (`userid`) VALUES ('6'),('7'),('8'),('9'),('10')
time="2022-04-20T16:47:57+08:00" level=info msg="[{0 6} {0 7} {0 8} {0 9} {0 10}]"

• resolvent 1： Write a BeforeCreate Hook function

func (user *User_1) BeforeCreate(tx *gorm.DB) error {
    
	var sequenceId uint
	if err := tx.Raw("SELECT NEXT VALUE FOR seq_for_autoid;").First(&sequenceId).Error; err != nil {
    
		return err
	}
	user.AutoId = sequenceId
	return nil
}

func main() {
    

	db, err := gorm.Open(mysql.Open("test:test@tcp(127.0.0.1:4000)/apiserver?charset=utf8mb4&parseTime=true&loc=Local"), &gorm.Config{
    
		NamingStrategy: schema.NamingStrategy{
    
			SingularTable: false,
		},
	})
	if err != nil {
    
		return
	}

	//db.AutoMigrate(&User{})

	var users []User_1
	users = append(users, User_1{
    UserId: "aaa"})
	users = append(users, User_1{
    UserId: "bbb"})

	db.Debug().Table("user_1").Create(&users)
	db.Debug().Table("user_2").Create(&users)
	db.Debug().Table("user_3").Create(&users)

	logrus.Info(users)
}

// Execution results , But every time you insert select once , It's slow when you write frequently 
[1.288ms] [rows:1] SELECT NEXT VALUE FOR seq_for_autoid;

[0.999ms] [rows:1] SELECT NEXT VALUE FOR seq_for_autoid;

[15.168ms] [rows:2] INSERT INTO `user_3` (`userid`,`autoid`) VALUES ('aaa',48),('bbb',49)
time="2022-04-20T17:24:14+08:00" level=info msg="[{48 aaa} {49 bbb}]"

• resolvent 2： Query after batch insertion last_sequence

This method only needs to be queried once , But inserts and queries lastId Be sure to put it in the same transaction , Otherwise, it will lead to lastid Get exception

func main() {
    

	var users []User
	users = append(users, User{
    UserId: "oopoo"})
	users = append(users, User{
    UserId: "nomonno"})

	var lastId uint64
	if err = db.Debug().Transaction(func(tx *gorm.DB) error {
    
		if err := tx.Table("user_1").Create(&users).Error; err != nil {
    
			return err
		}
        // This step queries the last one inserted last time sequence_id
		if err := tx.Raw("SELECT lastval(seq_for_autoid);").First(&lastId).Error; err != nil {
    
			return err
		}
        // Manual assignment 
		for i := 0; i < len(users); i++ {
    
			users[len(users)-i-1].AutoId = uint(lastId) - uint(i)
		}
		return nil
	}); err != nil {
    
		logrus.Error(err)
		return
	}

	logrus.Info(users)
}