The cache operation in the Insert method in the model generated by goctl is invalid when using the MySQL auto-increment primary key ID

The cache operation in the Insert method in the model generated by goctl is invalid when using the MySQL auto-increment primary key ID.

If you are using the MySQL auto-increment primary key ID.

CREATE TABLE `agent` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
  ....
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Then use goctl to create the model code, you will get the following code for the insert method:

 func (m *defaultAgentModel) Insert(ctx context.Context, data *Agent) (sql.Result, error) {
    agentIdKey := fmt.Sprintf("%s%v", cacheAgentIdPrefix, data.Id)
    ret, err := m.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (result sql.Result, err error) {
        query := fmt.Sprintf("insert into %s (%s) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", m.table, agentRowsExpectAutoSet)
        return conn.ExecCtx(ctx, query, data.AgentType, data.UserId, data.Name,...)
    }, agentIdKey)
    return ret, err
}

Because ID is generated by database increment, the ID in the key is 0. So it will trigger an operation to delete a cache with ID 0, which is meaningless and increases overhead.

slow   [REDIS] slowcall on executing: del cache:agent:id:0

I understand that this delete operation is to clear the placeholders that were built to prevent cache penetration, and this mechanism works when the ID is not set automatically but manually. However, the mechanism obviously doesn't work properly when the ID is relying on the database's auto increment, and it's not possible to remove the placeholder. This might cause other issues, for example, if the user has accessed the data with the new id, a placeholder is generated, and for a period of time, the user's request will read an error that the data does not exist.

In gorm, the Create method will assigned to the ID set as the auto-increment primary key. Even if go-zero is unable to adopt a similar design, it should solve this problem and delete the correct placeholders, to avoid hitting the wrong cache and producing the wrong results.

zeromicro / go-zero

The cache operation in the Insert method in the model generated by goctl is invalid when using the MySQL auto-increment primary key ID #3919