Sharded Table Management

Last updated: 2020-08-26 10:14:12

    This documents takes a sharded table as an example to describe some simple database operations in TDSQL.

    Creating a Table

    • For more information on the differences between sharded, non-sharded, and broadcast tables, see Table Overview.
    • For selection restrictions on the shardkey, see Shardkey Overview.
    • When creating a sharded table, the shardkey needs to be specified. Below is the sample code:
      mysql> create table test1(id int primary key,name varchar(20),addr varchar(20))shardkey=id;
      Query OK,0 rows affected(0.15 sec)

    Inserting Data

    Note:

    The insert field must include the shardkey; otherwise, the operation will be refused.

    Insert data into the table just created. Below is the sample code:

    mysql> insert into test1(id,name);
    Query OK,1 rows affected(0.08 sec)
    mysql> insert into test3(name,addr) values('example','shenzhen');
    ERROR 7013 (HY000): Proxy ERROR:get_shardkeys return error

    Querying Data

    Note:

    When you query data, you are recommended to include the shardkey, so that the distributed route will be automatically redirected to the corresponding shard, achieving the highest efficiency; otherwise, the distributed system will automatically scan the entire table and then aggregate the results at the gateway, which compromises the efficiency.

    Below is the sample code for data query:

    mysql> select id from test1 where id=1;

    Deleting Data

    Note:

    A "where" condition must be included in a "DELETE" operation, and you are recommended to include the shardkey in the "where" condition.

    Below is the sample code for data deletion:

    mysql> delete from test1 where id=1;
    Query OK, 1 row affected (0.02 sec)

    Was this page helpful?

    Was this page helpful?

    • Not at all
    • Not very helpful
    • Somewhat helpful
    • Very helpful
    • Extremely helpful
    Send Feedback
    Help