设置分片规则的DDL语句
更新时间 2025-06-17 10:58:35
最近更新时间: 2025-06-17 10:58:35
DRDS支持直接连接dbproxy执行DDL语句，本文为您介绍设置分片规则的DDL语句。
设置分片规则
各种表类型的分片规则DDL语法，如下表所示：
| 表类型 | 语法 | 示例 |
|---|---|---|
| 分片表 | sharding @@table name='' set type='sharding' and sharding_algo='' and sharding_id='' [ and algo_parameter='algo_value']… [and dn='']; | sharding @@table name='employee' set type='sharding' and sharding_algo='PartitionByEnumAndStringMod' and map_file='{"BJ":"0","SH":"1"}' and default_node='2' and sharding_id='employee_id' and sharding_area='area_id' and dn='drdstest_1,drdstest_2,drdstest_3'; |
| 分片表+库内分表 | sharding @@table name='' set type='inner' and sharding_algo='' and sharding_id='' [ and algo_parameter='algo_value']… and inner_sharding_algo='' and inner_sharding_id='' [ and inner_algo_parameter='algo_value']… and inner_total='' [and dn='']; | sharding @@table name=’employee’ set type='inner' and sharding_algo='PartitionByFileMap' and map_file='{"1":"0","2":"1"}' and default_node='0' and sharding_id='employee_id' and inner_sharding_algo='PartitionByDateRange' and inner_sharding_id='area_id' and inner_start_date='2018-07-10' and inner_time_unit_len='1' and inner_time_unit_type='year' and inner_total='2' and dn='drdstest_1,drdstest_2'; |
| 分桶表 | sharding @@table name='' set type='sharding' and sharding_algo='' and sharding_id='' [ and algo_parameter='algo_value']… and buckets=’’ [and dn='']; | sharding @@table name='bucket' set type='sharding' and sharding_algo='PartitionByFileMap' and map_file='{"1":"0","2":"1","3":"2"}' and default_node='0' and sharding_id='employee_id' and buckets='2'; |
| 单片表+库内分表 | sharding @@table name='' set type=' inner ' and inner_sharding_algo='' and sharding_id='' [ and inner_algo_parameter='algo_value']… and inner_total='' and dn=''; | sharding @@table name='single_inner' set type='inner' and inner_sharding_algo='PartitionByMod' and inner_sharding_id='employee_id' and inner_total='2' and dn='drdstest_1'; |
| 单片表 | sharding @@table name='' set type=' single ' and dn=''; | sharding @@table name='single' set type='single' and dn='drdstest_1'; |
| 全局表 | sharding @@table name='' set type='global' [and dn='']; | sharding @@table name='global' set type='global'; |
分片算法
设置分片规则时需要使用到分片算法，总共有6种分片算法，如下表所示：
| 分片算法名称 | 分片算法ID | 算法参数及示例 | 是否可用于库内分表 |
|---|---|---|---|
| 取模分片 | PartitionByMod | 是 | |
| 字符串hashCode取模分片 | PartitionByStringMod | 是 | |
| 枚举分片 | PartitionByFileMap |
| 否 |
| 时间范围分片 | PartitionByDateRange |
| 是 |
| 枚举分组取模分片 | PartitionByEnumAndMod |
| 否 |
| 枚举分组字符串hashCode取模分片 | PartitionByEnumAndStringMod |
| 否 |
