CREATE SHARDING TABLE RULE

Description

The CREATE SHARDING TABLE RULE syntax is used to add sharding table rule for the currently selected database

Syntax

Grammar Railroad diagram

  1. CreateShardingTableRule ::=
  2.   'CREATE' 'SHARDING' 'TABLE' 'RULE' ifNotExists? (tableRuleDefinition | autoTableRuleDefinition) (',' (tableRuleDefinition | autoTableRuleDefinition))*
  4. ifNotExists ::=
  5.   'IF' 'NOT' 'EXISTS'
  7. tableRuleDefinition ::= 
  8.   ruleName '(' 'DATANODES' '(' dataNode (',' dataNode)* ')' (','  'DATABASE_STRATEGY' '(' strategyDefinition ')')? (','  'TABLE_STRATEGY' '(' strategyDefinition ')')? (','  'KEY_GENERATE_STRATEGY' '(' keyGenerateStrategyDefinition ')')? (',' 'AUDIT_STRATEGY' '(' auditStrategyDefinition ')')? ')'
  10. autoTableRuleDefinition ::=
  11.   ruleName '(' 'STORAGE_UNITS' '(' storageUnitName (',' storageUnitName)*  ')' ',' 'SHARDING_COLUMN' '=' columnName ',' algorithmDefinition (',' 'KEY_GENERATE_STRATEGY' '(' keyGenerateStrategyDefinition ')')? (',' 'AUDIT_STRATEGY' '(' auditStrategyDefinition ')')? ')'
  13. strategyDefinition ::=
  14.   'TYPE' '=' strategyType ',' ('SHARDING_COLUMN' | 'SHARDING_COLUMNS') '=' columnName ',' algorithmDefinition
  16. keyGenerateStrategyDefinition ::= 
  17.   'KEY_GENERATE_STRATEGY' '(' 'COLUMN' '=' columnName ',' algorithmDefinition ')' 
  19. auditStrategyDefinition ::= 
  20.   'AUDIT_STRATEGY' '(' algorithmDefinition (',' algorithmDefinition)* ')'
  22. algorithmDefinition ::=
  23.   'TYPE' '(' 'NAME' '=' algorithmType (',' propertiesDefinition)?')'
  25. propertiesDefinition ::=
  26.   'PROPERTIES' '(' key '=' value (',' key '=' value)* ')'
  28. key ::=
  29.   string
  31. value ::=
  32.   literal
  34. ruleName ::=
  35.   identifier
  37. dataNode ::=
  38.   string
  40. storageUnitName ::=
  41.   identifier
  43. columnName ::=
  44.   identifier
  46. strategyType ::=
  47.   string
  49. algorithmType ::=
  50.   string

Supplement

  • tableRuleDefinition is defined for standard sharding table rule; autoTableRuleDefinition is defined for auto sharding table rule. For standard sharding rules and auto sharding rule, refer to Data Sharding;
  • use standard sharding table rule:
    • DATANODES can only use resources that have been added to the current database, and can only use INLINE expressions to specify required resources;
    • DATABASE_STRATEGY, TABLE_STRATEGY are the database sharding strategy and the table sharding strategy, which are optional, and the default strategy is used when not configured;
    • The attribute TYPE in strategyDefinition is used to specify the type of Sharding Algorithm, currently only supports STANDARD, COMPLEX. Using COMPLEX requires specifying multiple sharding columns with SHARDING_COLUMNS.
  • use auto sharding table rule:
    • STORAGE_UNITS can only use storage units that have been registered to the current database, and the required storage units can be specified by enumeration or INLINE expression;
    • Only auto sharding algorithm can be used, please refer to Auto Sharding Algorithm.
  • algorithmType is the sharding algorithm type, please refer to Sharding Algorithm;
  • The auto-generated algorithm naming rule is tableName _ strategyType _ shardingAlgorithmType;
  • The auto-generated primary key strategy naming rule is tableName _ strategyType;
  • KEY_GENERATE_STRATEGY is used to specify the primary key generation strategy, which is optional. For the primary key generation strategy, please refer to Distributed Primary Key;
  • AUDIT_STRATEGY is used to specify the sharding audit strategy, which is optional. For the sharding audit generation strategy, please refer to Sharding Audit;
  • ifNotExists clause is used for avoid Duplicate sharding rule error.

Example

1.Standard sharding table rule

  1. CREATE SHARDING TABLE RULE t_order_item (
  2. DATANODES("ds_${0..1}.t_order_item_${0..1}"),
  3. DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
  4. TABLE_STRATEGY(TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_order_item_${order_id % 2}")))),
  5. KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake")),
  6. AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true)
  7. );

2.Auto sharding table rule

  1. CREATE SHARDING TABLE RULE t_order (
  2. STORAGE_UNITS(ds_0,ds_1),
  3. SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
  4. KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake")),
  5. AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true)
  6. );

3.Create sharding rule with ifNotExists clause

  • Standard sharding table rule
  1. CREATE SHARDING TABLE RULE IF NOT EXISTS t_order_item (
  2. DATANODES("ds_${0..1}.t_order_item_${0..1}"),
  3. DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
  4. TABLE_STRATEGY(TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_order_item_${order_id % 2}")))),
  5. KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake")),
  6. AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true)
  7. );
  • Auto sharding table rule
  1. CREATE SHARDING TABLE RULE IF NOT EXISTS t_order (
  2. STORAGE_UNITS(ds_0,ds_1),
  3. SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
  4. KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake")),
  5. AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true)
  6. );

Reserved word

CREATE, SHARDING, TABLE, RULE, DATANODES, DATABASE_STRATEGY, TABLE_STRATEGY, KEY_GENERATE_STRATEGY, STORAGE_UNITS, SHARDING_COLUMN, TYPE, SHARDING_COLUMN, KEY_GENERATOR, SHARDING_ALGORITHM, COLUMN, NAME, PROPERTIES, AUDIT_STRATEGY, AUDITORS, ALLOW_HINT_DISABLE