This document takes a sharded table as an example to describe some simple database operations in TDSQL for MySQL.
mysql> create table test1(id int primary key,name varchar(20),addr varchar(20))shardkey=id; Query OK,0 rows affected(0.15 sec)
The shardkey must be included in an
INSERTstatement or else the operation will be denied.
Insert data into the table just created. The sample code is as follows:
mysql> insert into test1(id,name) VALUES(1,'test'); 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
When you query data, we recommend that you include the shardkey in the statement so that the request will be automatically routed to the corresponding shard according to the distributed routes, achieving the highest efficiency; otherwise, TDSQL will automatically scan the entire table and then aggregate the results at the gateway, which compromises the efficiency.
The sample code for querying data is as follows:
mysql> select id from test1 where id=1;
WHEREclause must be included in a
DELETEstatement, and we recommend including the shardkey in the
The sample code for deleting data is as follows:
mysql> delete from test1 where id=1; Query OK, 1 row affected (0.02 sec)