tencent cloud

$0 14-Day TrialExperience EdgeOne for acceleration and security protection!

Feedback

TDSQL for MySQL

Creating Table

Last updated: 2024-01-06 17:34:55

Creating a Sharded Table

When creating a sharded table, you must specify the value of shardkey at the end of the SQL statement. The value of shardkey is a field name in the table and will be used for subsequent SQL routing.
mysql> create table test1 ( a int, b int, c char(20),primary key (a,b),unique key u_1(a,c) ) shardkey=a;
Query OK, 0 rows affected (0.07 sec)
In a TDSQL instance, the shardkey corresponds to the partition field of the backend database, so it must be part of all primary keys and unique indexes; otherwise, the table cannot be created.
Use case : an error occurs when there are multiple unique indexes.
mysql> create table test1 ( a int, b int, c char(20),primary key (a,b),unique key u_1(a,c),unique key u_2(b,c) ) shardkey=a;;
According to the above SQL statement, there is a unique index u_2 that does not include the shardkey field, so the table cannot be created and the following error message will be displayed:
ERROR 1105 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
The index on the primary key or unique key has to be globally unique. To do so, the index must include the shardkey field.
In addition to the above restrictions, the shardkey field has the following requirements:
The type of the shardkey field must be int, bigint, smallint, char, or varchar.
The value of the shardkey field should not contain Asian characters, as the proxy will not convert the character set, and different character sets may be routed to different partitions.
Do not update the value of the shardkey field.
shardkey=a should be placed at the end of the SQL statement.
We recommend that you specify the value of shardkey in SQL statements when accessing data. This is not mandatory, but SQL statements without shardkey will be routed to all nodes and thus consume more resources.

Creating a Broadcast Table

You can create small tables (broadcast tables). Each set has all the data of a small table, which makes it easier to perform cross-set joins. In addition, distributed transactions ensure the atomicity of the modification operations, so that the data in all sets is exactly identical.
mysql> create table global_table ( a int, b int key) shardkey=noshardkey_allset;
Query OK, 0 rows affected (0.06 sec)

Creating a Non-sharded Table

You can create non-sharded tables using the same syntax as MySQL. All the data in this type of tables is stored in the first set.
mysql> create table noshard_table ( a int, b int key);
Query OK, 0 rows affected (0.02 sec)
Contact Us

Contact our sales team or business advisors to help your business.

Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

7x24 Phone Support
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon