TDSQL MySQL版建表-开发指南-文档中心-腾讯云

建分表
分表创建时必须在最后面指定分表键（shardkey）的值，该值为表中的一个字段名字，会用于后续 SQL 的路由选择：
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)
在分布式实例中，shardkey 对应后端数据库的分区字段，因此每一个唯一索引和主键都必须要包含这个 shardkey，否则无法创建表。
场景：存在多个唯一索引时报错。
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;
此时有一个唯一索引u_2不包含 shardkey，无法创建表，会报如下错误：
ERROR 1105 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
因为主键索引或者 unique key 索引意味着需要全局唯一，而要实现全局唯一索引，则必须包含 shardkey 字段。
除上面的限制外，shardkey 字段还有如下要求：
shardkey 字段的类型必须是 int、bigint、smallint、char、varchar、decimal、float、varbinary。
shardkey 字段类型为 char、varchar 时需定义字段长度。
shardkey 字段的值不能有中文，proxy 不会转换字符集，因此不同字符集可能会路由到不同的分区。
不能 update shardkey 字段的值。
shardkey=a 放在 SQL 的最后面。
访问数据尽量都带上 shardkey 字段，非强制要求，但是不带 shardkey 的 SQL 会路由到所有节点，消耗较多资源。
另外，创建表的时候，可以使用自增序列作为某一列的默认值，并把这一列设置为主键。
CREATE TABLE test2(column1 INT,column2 INT,column3 INT AUTO_INCREMENT,PRIMARY KEY(column3)) AUTO_INCREMENT=5 shardkey=column3;
插入数据时，设置为自增序列的列插入Null 值。
  INSERT INTO test2(column1,column2,column3) VALUES(0,0,NULL);
一级分区分表键（shardkey）规则：
在 TDSQL 中，分表也叫一级分区表。有 hash、range、list 三种规则。一级 hash 分区使用 shardkey 关键字指定拆分键。range 和 list 分区使用tdsql_distributed by 语法指定拆分键。
注意：
DB 5.7版本不支持 TDSQL_DISTRIBUTED BY range|list 的语法。
创建一级 HASH 分区表：
支持类型：
DATE，DATETIME
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
CHAR，VARCHAR
注意：
Shardkey 字段必须是主键以及所有唯一索引的一部分。
Shardkey 字段的值不能为中文，因为 Proxy 不会转换字符集，所以不同字符集可能会路由到不同的分区。
Shardkey=a 需放在 SQL 语句的最后。
使用场景
对于高并发业务场景的事务表，读写事务频繁，适合单表数据设计容量规模达到千万级别，且数据在可预见期内快速增长的场景。建议单分片容量不超过2TB，单分片对应的分表不超过2千万行，容量10GB-100GB之间。
​ 如业务订单表，数据设计容量规模预计在6千万，需要进行一级分区做水平切分，实例分布4个 set，则分区表平均量级大概在1500万行；如果后续增长规模较快超过初期容量设计，例如达到1亿，则平均单 set 分表可达2500万行，此时就当考虑做节点组 set 水平扩容，以保证单 set 分表数据量在合理范围。
基础语法
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition)]
[local_table_options]
shardkey=column_name
﻿
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
}
﻿
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
| data_type
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
}
﻿
key_part: {col_name [(length)]} [ASC | DESC]
﻿
index_type:
USING {BTREE}
﻿
index_option: {
index_type | COMMENT 'string'
}
[local_table_options]
Local_table_option: {AUTO_INCREMENT [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| ENGINE [=] engine_name
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value)
}
示例
DROP TABLE IF EXISTS employees_hash;
CREATE TABLE `employees_hash` (
`id`int NOT NULL,
`city` varchar(10),
`fired` DATE NOT NULL DEFAULT '1970.01.01',
PRIMARY KEY(id)
) shardkey=id;
﻿
DROP TABLE IF EXISTS s2;
CREATE TABLE s2 (
hiredate date,
data1 int,
data2 char(2),
primary key(hiredate, data1)
) shardkey=hiredate;
﻿
DROP TABLE IF EXISTS s3;
CREATE TABLE s3 (
name char(4),
data1 int,
data2 int,
primary key(name, data2)
) shardkey=name;
创建一级 Range|List 分区表：
使用场景：
一级分区除了根据 shardkey 进行一致性 hash 分区，也可以根据实际场景进行 Range 或 List 进行一级分区。
Range 分区是指根据一定字段取值范围进行分区指定，适合例如日志流水表，可以根据一定日期范围进行分区，方便后续数据归档。
List 分区则是根据字段的散列值进行匹配分区，例如人口统计表按省份进行 List 分区，可以有针对性地对地区属性进行分区管理。
基础语法
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition)]
[local_table_options]
TDSQL_DISTRIBUTED BY range|list (column_name) [partition_options]
﻿
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
}
﻿
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
| data_type
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
}
﻿
key_part: {col_name [(length)]} [ASC | DESC]
﻿
index_type:
USING {BTREE}
﻿
index_option: {
index_type | COMMENT 'string'
}
[local_table_options]
Local_table_option: {AUTO_INCREMENT [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| ENGINE [=] engine_name
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value)
}
partition_options:
PARTITION BY
| RANGE{(expr)}
| LIST{(expr)}
[(partition_definition [, partition_definition] ...)]
﻿
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string']
一级 RANGE 分区
支持类型：
DATE，DATETIME，TIMESTAMP
TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT
CHAR，VARCHAR
说明：
如果分区键是 char 或者 varchar 类型，建议长度不超255。
禁止：
避免使用 TIMESTAMP 类型作为分区键，因为 timestamp 受到时区的影响，同时只能使用到2038年。
示例：
create table t1(a int key, b int) tdsql_distributed by range(a) (s1 values less than(100), s2 values less than(200));
一级 LIST 分区
支持类型：
DATE，DATETIME，TIMESTAMP
TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT
CHAR，VARCHAR
注意：
分区键为字符串时，不要使用中文。
tdsql_distributed by ...语法放置于 create table ...的末尾。
创建一级 list 分区表语句中指定的 s1和 s2是每个 set 的别名，基于实现原理，s1、s2不能自定义，只能按照顺序依次命名为 s1、s2…
set 的别名可通过/proxy/show status;获取到。
DB 5.7版本不支持 TDSQL_DISTRIBUTED BY range|list 的语法。
示例：
DROP TABLE IF EXISTS employees_list;
CREATE TABLE `employees_list` (
`id`int NOT NULL,
`city` varchar(10),
`fired` DATE NOT NULL DEFAULT '1970.01.01',
PRIMARY KEY(id)
)
TDSQL_DISTRIBUTED BY LIST(id) (
s1 VALUES IN (1,3,5),
s2 VALUES IN (2,4,6)
);
--查看set_1624363222_1和set_1624363251_3的别名分别为s1和s2
二级分区分表键（shardkey）的规则请参考：
﻿二级分区 
建单表
支持建立普通的表，语法和 MySQL 完全一致，此时该表的数据全量存在第一个 set 中，所有该类型的表都放在第一个 set 中：
mysql> create table noshard_table ( a int, b int key);
Query OK, 0 rows affected (0.02 sec)
建广播表
支持建小表（广播表），此时该表在所有 set 中都是全量数据，主要方便于跨 set 的 join 操作，同时通过分布式事务保证修改操作的原子性，使得所有 set 的数据完全一致。
﻿mysql> create table global_table ( a int, b int key) shardkey=noshardkey_allset;
Query OK, 0 rows affected (0.06 sec)
建表

本页目录：

建分表

建单表

建广播表
