TiDB分表唯一主键ID——sequence 与gorm无法获取主键的解决

按理说用了TiDB后就不用去考虑分库分表的问题了，但是由于业务场景上同时也要支持MySQL，且确实数据上存在一定隔离性，因此通过数据类型进行水平分表了。

MySQL水平分表后的主键id通过snowflake来写入，而TiDB的主键id决定通过tidb的sequenceID来实现

Sequence

Sequence 是数据库系统按照一定规则自增的数字序列，具有唯一和单调递增的特性。在官方 SQL 2003 标准中，其被定义为"生成连续数值的一种机制，Sequence 既可以是内部对象，也可以是外部对象"。因为原生 MySQL 中并未支持 Sequence，所以 TiDB Sequence 的语法参考了 MariaDB、Oracle 和 IBM Db2

• Create Sequence 语法

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 语法

SHOW CREATE SEQUENCE sequence_name

• Drop Sequence

DROP [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name

• 获取下一个值

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

示例

创建同一结构的三张表，表根据业务类型进行分表

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 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表：

user_2表：

user_3表：

gorm的批量插入接口插入，无法返回获取到的主键值

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)
}

//运行结果
[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}]"

• 解决方法1：写一个BeforeCreate钩子函数

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)
}

//执行结果，但是每次插入都要select一次，写频繁时会很慢
[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}]"

• 解决方法2：批量插入完后查询last_sequence

这种方法只需要查询一次，但是插入和查询lastId一定要放到同一个事务当中，否则会导致lastid获取异常

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
		}
        //这一步查询上一次插入的最后一个sequence_id
		if err := tx.Raw("SELECT lastval(seq_for_autoid);").First(&lastId).Error; err != nil {
			return err
		}
        //手动赋值
		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)
}