数据分片
- 语法说明
- 示例

数据分片

语法说明

Sharding Table Rule

CREATE SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] ...
CREATE DEFAULT SHARDING shardingScope STRATEGY (shardingStrategy)
ALTER SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] ...
DROP SHARDING TABLE RULE tableName [, tableName] ...
CREATE SHARDING ALGORITHM shardingAlgorithmDefinition [,  shardingAlgorithmDefinition] ...
ALTER SHARDING ALGORITHM shardingAlgorithmDefinition [,  shardingAlgorithmDefinition] ...
DROP SHARDING ALGORITHM algorithmName [, algorithmName] ...
shardingTableRuleDefinition:
    shardingAutoTableRule | shardingTableRule
shardingAutoTableRule:
    tableName(resources COMMA shardingColumn COMMA algorithmDefinition (COMMA keyGenerateDeclaration)?)
shardingTableRule:
    tableName(dataNodes (COMMA  databaseStrategy)? (COMMA tableStrategy)? (COMMA keyGenerateDeclaration)?)
resources:
    RESOURCES(resource [, resource] ...)
dataNodes:
    DATANODES(dataNode [, dataNode] ...)
resource:
    resourceName | inlineExpression
dataNode:
    resourceName | inlineExpression
shardingColumn:
    SHARDING_COLUMN=columnName
algorithmDefinition:
    TYPE(NAME=shardingAlgorithmType [, PROPERTIES([algorithmProperties])])
keyGenerateDeclaration:
    keyGenerateDefinition | keyGenerateConstruction
keyGenerateDefinition:
    GENERATED_KEY(COLUMN=columnName, strategyDefinition)
shardingScope:
    DATABASE | TABLE
databaseStrategy:
    DATABASE_STRATEGY(shardingStrategy)
tableStrategy:
    TABLE_STRATEGY(shardingStrategy)
keyGenerateConstruction
    GENERATED_KEY(COLUMN=columnName, GENERATED_KEY_ALGORITHM=keyGenerateAlgorithmName)
shardingStrategy:
    TYPE=strategyType, shardingColumn, shardingAlgorithm
shardingColumn:
    SHARDING_COLUMN=columnName
shardingAlgorithm:
    existingAlgorithm | autoCreativeAlgorithm
existingAlgorithm:
    SHARDING_ALGORITHM=shardingAlgorithmName
autoCreativeAlgorithm:
    SHARDING_ALGORITHM(algorithmDefinition)
strategyDefinition:
    TYPE(NAME=keyGenerateStrategyType [, PROPERTIES([algorithmProperties])])
shardingAlgorithmDefinition:
    shardingAlgorithmName(algorithmDefinition)
algorithmProperties:
    algorithmProperty [, algorithmProperty] ...
algorithmProperty:
    key=value

RESOURCES 需使用 RDL 管理的数据源资源
shardingAlgorithmType 指定自动分片算法类型，请参考自动分片算法
keyGenerateStrategyType 指定分布式主键生成策略，请参考分布式主键
重复的 tableName 将无法被创建
shardingAlgorithm 能够被不同的 Sharding Table Rule 复用，因此在执行 DROP SHARDING TABLE RULE 时，对应的 shardingAlgorithm 不会被移除
如需移除 shardingAlgorithm，请执行 DROP SHARDING ALGORITHM
strategyType 指定分片策略，请参考分片策略
Sharding Table Rule 同时支持 Auto Table 和 Table 两种类型，两者在语法上有所差异，对应配置文件请参考数据分片
使用 autoCreativeAlgorithm 方式指定 shardingStrategy 时，将会自动创建新的分片算法，算法命名规则为 tableName_strategyType_shardingAlgorithmType，如 t_order_database_inline

Sharding Binding Table Rule

CREATE SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
ALTER SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
DROP SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
bindTableRulesDefinition:
    (tableName [, tableName] ... )

ALTER 会使用新的配置直接覆盖数据库内的绑定表配置

Sharding Broadcast Table Rule

CREATE SHARDING BROADCAST TABLE RULES (tableName [, tableName] ...)
ALTER SHARDING BROADCAST TABLE RULES (tableName [, tableName] ...)
DROP SHARDING BROADCAST TABLE RULES (tableName [, tableName] ...)

ALTER 会使用新的配置直接覆盖数据库内的广播表配置

示例

Sharding Table Rule

Key Generator

CREATE SHARDING KEY GENERATOR snowflake_key_generator (
TYPE(NAME=SNOWFLAKE, PROPERTIES("worker-id"=123))
);
ALTER SHARDING KEY GENERATOR snowflake_key_generator (
TYPE(NAME=SNOWFLAKE, PROPERTIES("worker-id"=456))
);
DROP SHARDING KEY GENERATOR snowflake_key_generator;

Auto Table

CREATE SHARDING TABLE RULE t_order (
RESOURCES(resource_0,resource_1),
SHARDING_COLUMN=order_id,TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
GENERATED_KEY(COLUMN=another_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
);
ALTER SHARDING TABLE RULE t_order (
RESOURCES(resource_0,resource_1,resource_2,resource_3),
SHARDING_COLUMN=order_id,TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=16)),
GENERATED_KEY(COLUMN=another_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
);
DROP SHARDING TABLE RULE t_order;
DROP SHARDING ALGORITHM t_order_hash_mod;

Table

CREATE SHARDING ALGORITHM table_inline (
TYPE(NAME=inline,PROPERTIES("algorithm-expression"="t_order_item_${order_id % 2}"))
);
CREATE SHARDING TABLE RULE t_order_item (
DATANODES("resource_${0..1}.t_order_item_${0..1}"),
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME=inline,PROPERTIES("algorithm-expression"="resource_${user_id % 2}")))),
TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=table_inline),
GENERATED_KEY(COLUMN=another_id,GENERATED_KEY_ALGORITHM=snowflake_key_generator)
);
ALTER SHARDING ALGORITHM database_inline (
TYPE(NAME=inline,PROPERTIES("algorithm-expression"="resource_${user_id % 4}"))
),table_inline (
TYPE(NAME=inline,PROPERTIES("algorithm-expression"="t_order_item_${order_id % 4}"))
);
ALTER SHARDING TABLE RULE t_order_item (
DATANODES("resource_${0..3}.t_order_item${0..3}"),
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=table_inline),
GENERATED_KEY(COLUMN=another_id,GENERATED_KEY_ALGORITHM=snowflake_key_generator)
);
DROP SHARDING TABLE RULE t_order_item;
DROP SHARDING ALGORITHM database_inline;
CREATE DEFAULT SHARDING DATABASE STRATEGY (
TYPE = standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=algorithmsName
);

Sharding Binding Table Rule

CREATE SHARDING BINDING TABLE RULES (t_order,t_order_item),(t_1,t_2);
ALTER SHARDING BINDING TABLE RULES (t_order,t_order_item);
DROP SHARDING BINDING TABLE RULES;
DROP SHARDING BINDING TABLE RULES (t_order,t_order_item);

Sharding Broadcast Table Rule

CREATE SHARDING BROADCAST TABLE RULES (t_b,t_a);
ALTER SHARDING BROADCAST TABLE RULES (t_b,t_a,t_3);
DROP SHARDING BROADCAST TABLE RULES;
DROP SHARDING BROADCAST TABLE RULES t_b;