Individual API operations|V4.3.5|OceanBase Database|docs|OceanBase Documentations

Insert

The syntax is as follows:

// Insert a record by rowKey.
Insert(ctx context.Context, tableName string, rowKey []*table.Column, mutateColumns []*table.Column, opts ...ObkvOption) (int64, error)

Input parameters:

ctx: the API timeout period.
tableName: the name of the table.
rowKey: the primary key.
mutateColumns: the columns, other than the primary key, to be written.
opts: the additional options. This parameter is optional.

Response parameters:

int64: the number of affected rows.
error: the error code and error message.

Optional parameters:

ObOperationOption.WithFilter: specifies whether to insert the row based on the filter conditions. For more information about filters, see Filters.
ObOperationOption.WithScanRange: specifies whether to insert the row based on whether the rows within the range in the same partition meet the filter conditions. This parameter takes effect only in the INSERT operation.

Here is an example:

// CREATE TABLE test(c1 bigint, c2 bigint, PRIMARY KEY(c1)) PARTITION BY hash(c1) partitions 2;
func main() {
    const (
        configUrl    = "xxx"
        fullUserName = "user@tenant#cluster"
        passWord     = ""
        sysUserName  = "sysUser"
        sysPassWord  = ""
        tableName    = "test"
    )

    cfg := config.NewDefaultClientConfig()
    cli, err := client.NewClient(configUrl, fullUserName, passWord, sysUserName, sysPassWord, cfg)
    if err != nil {
        panic(err)
    }

    ctx, _ := context.WithTimeout(context.Background(), time.Duration(1000)*time.Millisecond) // 1000ms
    rowKey := []*table.Column{table.NewColumn("c1", int64(1))}
    mutateColumns := []*table.Column{table.NewColumn("c2", int64(2))}

    startRowKey := []*table.Column{table.NewColumn("c1", "1")}
    endRowKey := []*table.Column{table.NewColumn("c1", "10")}
    keyRanges := []*table.RangePair{table.NewRangePair(startRowKey, endRowKey)}

    affectRows, err := cli.Insert(
        ctx,
        tableName,
        rowKey,
        mutateColumns,
        option.WithFilter(filter.CompareVal(filter.Equal, "c2", int64(1))), // where c2 = 1
        option.WithScanRange(keyRanges),
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)
}

The example is described as follows:

(1, 2) is written to the test table. 1 is the value of the primary key c1, and 2 is the value of the regular column c2.
All rows within the range of 1 to 10 in the same partition as (1, 2) are checked. If a row with c2 = 1 exists, (1, 2) is written to the test table.
The ctx parameter sets the maximum timeout period for the INSERT operation to 1000 ms.

Get

The syntax is as follows:

// Insert a record by rowKey.
Insert(ctx context.Context, tableName string, rowKey []*table.Column, mutateColumns []*table.Column, opts ...ObkvOption) (int64, error)

Input parameters:

ctx: the API timeout period.
tableName: the name of the table.
rowKey: the primary key.
getColumns: the name of the column to be queried. If you set this parameter to nil, all columns are returned.
opts: the additional options. You do not need to specify this parameter.

Response parameters:

SingleResult: the result. You can call the Value(columnName string) interface{} method to query a value of a specified column.
error: the error code and error message.

Optional parameters:

ObOperationOption.WithFilter: specifies whether to insert the row based on the filter conditions. For more information about filters, see Filters.
ObOperationOption.WithScanRange: specifies whether to insert the row based on whether the rows within the range in the same partition meet the filter conditions. This parameter takes effect only in the INSERT operation.

Here is an example:

// CREATE TABLE test(c1 bigint, c2 bigint, PRIMARY KEY(c1)) PARTITION BY hash(c1) partitions 2;
func main() {
    // Init client
    // ...

    // insert
    ctx, _ := context.WithTimeout(context.Background(), time.Duration(1000)*time.Millisecond) // 1000ms
    rowKey := []*table.Column{table.NewColumn("c1", int64(1))}
    insertColumns := []*table.Column{table.NewColumn("c2", int64(2))}
    affectRows, err := cli.Insert(
        ctx,
        tableName,
        rowKey,
        insertColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)

    // get
    selectColumns := []string{"c1", "c2"}
    result, err := cli.Get(
        ctx,
        tableName,
        rowKey,
        selectColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(result.Value("c1"))
    fmt.Println(result.Value("c2"))
}

The example is described as follows:

(1, 2) is written to the test table. 1 is the value of the primary key c1, and 2 is the value of the regular column c2.
rowkey(c1=1) retrieves the record, and Value retrieves the values of c1 and c2.

Delete

The syntax is as follows:

// Delete a record by rowKey.
Delete(ctx context.Context, tableName string, rowKey []*table.Column, opts ...ObkvOption) (int64, error)

Input parameters:

ctx: the API timeout period.
tableName: the name of the table.
rowKey: the primary key.
opts: the additional options. You do not need to specify this parameter.

Response parameters:

int64: the number of affected rows.
error: the error code and error message.

Optional parameters:

ObOperationOption.WithFilter: specifies whether to insert the row based on the filter conditions. For more information about filters, see Filters.

Here is an example:

// CREATE TABLE test(c1 bigint, c2 bigint, PRIMARY KEY(c1)) PARTITION BY hash(c1) partitions 2;
func main() {
    // Init client
    // ...

    // insert
    ctx, _ := context.WithTimeout(context.Background(), time.Duration(1000)*time.Millisecond) // 1000ms
    rowKey := []*table.Column{table.NewColumn("c1", int64(1))}
    insertColumns := []*table.Column{table.NewColumn("c2", int64(2))}
    affectRows, err := cli.Insert(
        ctx,
        tableName,
        rowKey,
        insertColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)

    // delete
    affectRows, err = cli.Delete(
        ctx,
        tableName,
        rowKey,
        option.WithFilter(filter.CompareVal(filter.Equal, "c2", int64(2))),
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)
}

The example is described as follows:

(1, 2) is written to the test table. 1 is the value of the primary key c1, and 2 is the value of the regular column c2.
The record with rowkey(c1=1) and c2=2 is deleted.

Update

The syntax is as follows:

// Update a record by rowKey.
Update(ctx context.Context, tableName string, rowKey []*table.Column, mutateColumns []*table.Column, opts ...ObkvOption) (int64, error)

Input parameters:

ctx: the API timeout period.
tableName: the name of the table.
rowKey: the primary key.
mutateColumns: the columns, other than the primary key, to be updated.
opts: the additional options. You do not need to specify this parameter.

Response parameters:

int64: the number of affected rows.
error: the error code and error message.

Optional parameters:

ObOperationOption.WithFilter: specifies whether to insert the row based on the filter conditions. For more information about filters, see Filters.

Here is an example:

// CREATE TABLE test(c1 bigint, c2 bigint, PRIMARY KEY(c1)) PARTITION BY hash(c1) partitions 2;
func main() {
    // Init client
    // ...

    // insert
    ctx, _ := context.WithTimeout(context.Background(), time.Duration(1000)*time.Millisecond) // 1000ms
    rowKey := []*table.Column{table.NewColumn("c1", int64(1))}
    insertColumns := []*table.Column{table.NewColumn("c2", int64(2))}
    affectRows, err := cli.Insert(
        ctx,
        tableName,
        rowKey,
        insertColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)

    // update c2(2) -> c2(3)
    updateColumns := []*table.Column{table.NewColumn("c2", int64(3))}
    affectRows, err = cli.Update(
        ctx,
        tableName,
        rowKey,
        updateColumns,
        option.WithFilter(filter.CompareVal(filter.Equal, "c2", int64(3))),
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)
}

The example is described as follows:

(1, 2) is written to the test table. 1 is the value of the primary key c1, and 2 is the value of the regular column c2.
The record with rowkey(c1)=1 in the test table is updated. If the value of c2 corresponding to the row with c1=1 is 3, the value of c2 is updated from 2 to 3.

InsertOrUpdate

The syntax is as follows:

// InsertOrUpdate insert or update a record by rowKey.
// insert if the primary key does not exist, update if it does.
InsertOrUpdate(ctx context.Context, tableName string, rowKey []*table.Column, mutateColumns []*table.Column, opts ...ObkvOption) (int64, error)

Input parameters:

ctx: the API timeout period.
tableName: the name of the table.
rowKey: the primary key.
mutateColumns: the columns, other than the primary key, to be written or updated.
opts: the additional options. You do not need to specify this parameter.

Response parameters:

int64: the number of affected rows.
error: the error code and error message.

Here is an example:

// CREATE TABLE test(c1 bigint, c2 bigint, PRIMARY KEY(c1)) PARTITION BY hash(c1) partitions 2;
func main() {
    // Init client
    // ...

    // insert
    ctx, _ := context.WithTimeout(context.Background(), time.Duration(1000)*time.Millisecond) // 1000ms
    rowKey := []*table.Column{table.NewColumn("c1", int64(1))}
    mutateColumns := []*table.Column{table.NewColumn("c2", int64(2))}
    affectRows, err := cli.InsertOrUpdate(
        ctx,
        tableName,
        rowKey,
        mutateColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)

    // update
    mutateColumns = []*table.Column{table.NewColumn("c2", int64(3))}
    affectRows, err = cli.InsertOrUpdate(
        ctx,
        tableName,
        rowKey,
        mutateColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)
}

The example is described as follows:

The InsertOrUpdate operation writes (1, 2) to the test table. 1 is the value of the primary key c1, and 2 is the value of the regular column c2.
The InsertOrUpdate operation updates the record with rowkey(c1)=1 in the test table, and updates the value of c2 from 2 to 3.

Replace

The syntax is as follows:

// Replace a record by rowKey.
Replace(ctx context.Context, tableName string, rowKey []*table.Column, mutateColumns []*table.Column, opts ...ObkvOption) (int64, error)

Input parameters:

ctx: the API timeout period.
tableName: the name of the table.
rowKey: the primary key.
mutateColumns: the columns, other than the primary key, to be replaced.
opts: the additional options. You do not need to specify this parameter.

Response parameters:

int64: the number of affected rows.
error: the error code and error message.

Here is an example:

// CREATE TABLE test(c1 bigint, c2 bigint, PRIMARY KEY(c1)) PARTITION BY hash(c1) partitions 2;
func main() {
    // Init client
    // ...

    // insert
    ctx, _ := context.WithTimeout(context.Background(), time.Duration(1000)*time.Millisecond) // 1000ms
    rowKey := []*table.Column{table.NewColumn("c1", int64(1))}
    insertColumns := []*table.Column{table.NewColumn("c2", int64(2))}
    affectRows, err := cli.Insert(
        ctx,
        tableName,
        rowKey,
        insertColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)

    // replace c2(2) -> c2(3)
    replaceColumns := []*table.Column{table.NewColumn("c2", int64(3))}
    affectRows, err = cli.Replace(
        ctx,
        tableName,
        rowKey,
        replaceColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)
}

The example is described as follows:

(1, 2) is written to the test table. 1 is the value of the primary key c1, and 2 is the value of the regular column c2.
The record with rowkey(c1)=1 in the testtable is replaced to replace the value of c2 with 3.

Increment

The syntax is as follows:

// Increment a record by rowKey.
Increment(ctx context.Context, tableName string, rowKey []*table.Column, mutateColumns []*table.Column, opts ...ObkvOption) (SingleResult, error)

Input parameters:

ctx: the API timeout period.
tableName: the name of the table.
rowKey: the primary key.
mutateColumns: the columns, other than the primary key, to be incremented.
opts: the additional options. This parameter is optional.

Response parameters:

int64: the number of affected rows.
error: the error code and error message.

Optional parameters:

ObOperationOption.WithReturnRowKey: specifies whether to return the primary key.
ObOperationOption.WithReturnAffectedEntity: specifies whether to return the original row that was modified.
ObOperationOption.WithFilter: specifies whether to insert the row based on the filter conditions. For more information about filters, see Filters.

Notice

The increment operation is supported only on signed columns.

Here is an example:

// CREATE TABLE test(c1 bigint, c2 bigint, PRIMARY KEY(c1)) PARTITION BY hash(c1) partitions 2;
func main() {
    // Init client
    // ...

    // insert
    ctx, _ := context.WithTimeout(context.Background(), time.Duration(1000)*time.Millisecond) // 1000ms
    rowKey := []*table.Column{table.NewColumn("c1", int64(1))}
    insertColumns := []*table.Column{table.NewColumn("c2", int64(2))}
    affectRows, err := cli.Insert(
        ctx,
        tableName,
        rowKey,
        insertColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)

    // increment c2(2) -> c2(2) + (3) = c2(5)
    incrementColumns := []*table.Column{table.NewColumn("c2", int64(3))}
    res, err := cli.Increment(
        ctx,
        tableName,
        rowKey,
        incrementColumns,
        client.WithReturnRowKey(true),         // return rowKey if you need
        client.WithReturnAffectedEntity(true), // return result entity if you need
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(res.AffectedRows())
    fmt.Println(res.RowKey())
    fmt.Println(res.Value("c2"))
}

The example is described as follows:

(1, 2) is written to the test table. 1 is the value of the primary key c1, and 2 is the value of the regular column c2.
The value of c2 is incremented by 3 for the record with c1=1.
WithReturnRowKey(true) specifies to return the primary key.
WithReturnAffectedEntity(true) specifies to return the modified column. The result shows that the value of c2 is changed to 5.

Append

The syntax is as follows:

// Append a record by rowKey.
Append(ctx context.Context, tableName string, rowKey []*table.Column, mutateColumns []*table.Column, opts ...ObkvOption) (SingleResult, error)

Input parameters:

ctx: the API timeout period.
tableName: the name of the table.
rowKey: the primary key.
mutateColumns: the columns, other than the primary key, to which a string is to be appended.
opts: the additional options. This parameter is optional.

Response parameters:

int64: the number of affected rows.
error: the error code and error message.

Optional parameters:

ObOperationOption.WithReturnRowKey: specifies whether to return the primary key.
ObOperationOption.WithReturnAffectedEntity: specifies whether to return the original row that was modified.
ObOperationOption.WithFilter: specifies whether to insert the row based on the filter conditions. For more information about filters, see Filters.

Notice

OceanBase Database V3.x supports the append operation on varchar and varbinary columns.
OceanBase Database V4.x supports the append operation on char and varchar columns. The length of an appended column cannot exceed 1 MB.

Here is an example:

// CREATE TABLE test(c1 bigint, c2 varchar(20), PRIMARY KEY(c1)) PARTITION BY hash(c1) partitions 2;
func main() {
    // Init client
    // ...

    // insert
    ctx, _ := context.WithTimeout(context.Background(), time.Duration(1000)*time.Millisecond) // 1000ms
    rowKey := []*table.Column{table.NewColumn("c1", int64(1))}
    insertColumns := []*table.Column{table.NewColumn("c2", "hello")}
    affectRows, err := cli.Insert(
        ctx,
        tableName,
        rowKey,
        insertColumns,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(affectRows)

    // append c2(2) -> c2("hello") + (" oceanbase") = c2("hello oceanbase")
    appendColumns := []*table.Column{table.NewColumn("c2", " oceanbase")}
    res, err := cli.Append(
        ctx,
        tableName,
        rowKey,
        appendColumns,
        client.WithReturnRowKey(true),         // return rowKey if you need
        client.WithReturnAffectedEntity(true), // return result entity if you need
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(res.AffectedRows())
    fmt.Println(res.RowKey())
    fmt.Println(res.Value("c2"))
}

The example is described as follows:

(1, 'hello') is written to the test table. 1 is the value of the primary key c1, and hello is the value of the regular column c2.
oceanbase is appended to the value of c2 for the record with c1=1.
WithReturnRowKey(true) specifies to return the primary key.
WithReturnAffectedEntity(true) specifies to return the modified column. The result shows that the value of c2 is changed to hello oceanbase.