CREATE SHARDING TABLE RULE

Description

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

Syntax

  1. CreateShardingTableRule ::=
  2.   'CREATE' 'SHARDING' 'TABLE' 'RULE' ( tableDefinition | autoTableDefinition ) ( ',' ( tableDefinition | autoTableDefinition ) )*
  4. tableDefinition ::= 
  5.    tableName '(' 'DATANODES' '(' dataNode ( ',' dataNode )* ')' ( ',' 'DATABASE_STRATEGY' '(' strategyDefinition ')' )? ( ',' 'TABLE_STRATEGY' '(' strategyDefinition ')' )?  ( ',' 'KEY_GENERATE_STRATEGY' '(' keyGenerateStrategyDefinition ')' )? ( ',' 'AUDIT_STRATEGY' '(' auditStrategyDefinition ')' )? ')'
  7. autoTableDefinition ::=
  8.     tableName '(' 'RESOURCES' '(' resourceName ( ',' resourceName )*  ')' ',' '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 ',' ( 'KEY_GENERATOR' '=' algorihtmName | algorithmDefinition ) ')' 
  16. algorithmDefinition ::=
  17.   ('SHARDING_ALGORITHM' '=' algorithmName | 'TYPE' '(' 'NAME' '=' algorithmType ( ',' 'PROPERTIES'  '(' propertyDefinition  ')' )?')'  )
  19. propertyDefinition ::=
  20.   ( key  '=' value ) ( ',' key  '=' value )* 
  22. tableName ::=
  23.   identifier
  25. resourceName ::=
  26.   identifier
  28. columnName ::=
  29.   identifier
  31. auditorName ::=
  32.   identifier
  34. algorithmName ::=
  35.   identifier
  37. algorithmType ::=
  38.   string

Supplement

  • tableDefinition is defined for standard sharding table rule; autoTableDefinition 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:
    • RESOURCES can only use resources that have been added to the current database, and the required resources 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

  • Create standard sharding table rule by specifying sharding algorithms
  1. -- create sharding algorithms
  2. CREATE SHARDING ALGORITHM database_inline (
  3.     TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_${user_id % 2}"))
  4. ), table_inline (
  5.     TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_${order_id % 2}"))
  6. ); 
  8. -- create a sharding rule by specifying sharding algorithms
  9. CREATE SHARDING TABLE RULE t_order (
  10.     DATANODES("ds_${0..1}.t_order_${0..1}"),
  11.     DATABASE_STRATEGY(TYPE="standard", SHARDING_COLUMN=user_id, SHARDING_ALGORITHM=database_inline),
  12.     TABLE_STRATEGY(TYPE="standard", SHARDING_COLUMN=order_id, SHARDING_ALGORITHM=table_inline)
  13. );
  • Use the default sharding database strategy, create standard sharding table rule by specifying a sharding algorithm
  1. -- create sharding algorithms
  2. CREATE SHARDING ALGORITHM database_inline (
  3.     TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_${user_id % 2}"))
  4. ), table_inline (
  5.     TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_${order_id % 2}"))
  6. ); 
  8. -- create a default sharding database strategy
  9. CREATE DEFAULT SHARDING DATABASE STRATEGY (
  10.     TYPE="standard", SHARDING_COLUMN=order_id, SHARDING_ALGORITHM=database_inline
  11. );
  13. -- create a sharding table rule by specifying a sharding algorithm
  14. CREATE SHARDING TABLE RULE t_order (
  15.     DATANODES("ds_${0..1}.t_order_${0..1}"),
  16.     TABLE_STRATEGY(TYPE="standard", SHARDING_COLUMN=order_id, SHARDING_ALGORITHM=table_inline)
  17. );
  • Use both the default sharding and the default sharding strategy, create standard sharding table rule
  1. -- create sharding algorithms
  2. CREATE SHARDING ALGORITHM database_inline (
  3.     TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_${user_id % 2}"))
  4. ), table_inline (
  5.     TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_${order_id % 2}"))
  6. ); 
  8. -- create a default sharding database strategy
  9. CREATE DEFAULT SHARDING DATABASE STRATEGY (
  10.     TYPE="standard", SHARDING_COLUMN=order_id, SHARDING_ALGORITHM=database_inline
  11. );
  13. -- create a default sharding table strategy
  14. CREATE DEFAULT SHARDING TABLE STRATEGY (
  15.     TYPE="standard", SHARDING_COLUMN=order_id, SHARDING_ALGORITHM=table_inline
  16. );
  18. -- create a sharding table rule 
  19. CREATE SHARDING TABLE RULE t_order (
  20.     DATANODES("ds_${0..1}.t_order_${0..1}")
  21. );
  • Create standard sharding table rule and sharding algorithms at the same time
  1. CREATE SHARDING TABLE RULE t_order (
  2.     DATANODES("ds_${0..1}.t_order_${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=user_id, SHARDING_ALGORITHM(TYPE(NAME="inline", PROPERTIES("algorithm-expression"="ds_${order_id % 2}"))))
  5. );

2.Auto sharding table rule

  • create auto sharding table rule
  1. CREATE SHARDING TABLE RULE t_order (
  2.     RESOURCES(ds_0, ds_1),
  3.     SHARDING_COLUMN=order_id, TYPE(NAME="MOD", PROPERTIES("sharding-count"="4"))
  4. );

Reserved word

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