ALTER SHARDING TABLE RULE

Description

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

Syntax

Grammar Railroad diagram

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

Example

1.Standard sharding table rule

  1. ALTER SHARDING TABLE RULE t_order_item (
  2. DATANODES("ds_${0..3}.t_order_item${0..3}"),
  3. DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${user_id % 4}")))),
  4. TABLE_STRATEGY(TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_order_item_${order_id % 4}")))),
  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. ALTER SHARDING TABLE RULE t_order (
  2. STORAGE_UNITS(ds_0,ds_1,ds_2,ds_3),
  3. SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="16")),
  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

ALTER, 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