表相关ddl:create/alter/drop table
基础DDL语句包括:create table、alter table、drop table
create table只会在标准库中执行
alter table、drop table要根据分片规则的设置在标准库和对应的分片库上执行
UDAL对数据库建表DDL语句进行了扩展,支持设置分片规则。执行设置分片规则语句,dbproxy根据分片规则从标准库获取建表语句,再到对应的分片上执行。示例:
sharding @@table name='bill' set type='global' --设置全局表
sharding @@table name='bill' set type='single' and dn='dn1' --设置单片表
sharding @@table name='bill' set type='sharding' and sharding_func='mod-long-6' and sharding_id='sharding_id' and - sharding_area='sharding_area' and dn='dn1,dn2,dn3' --设置分片表(dn参数可选)
sharding @@table name='bill' set type='inner' and inner_total='10' and inner_sharding_id='inner_sharding_id' and inner_sharding_func = 'inner_sharding_func' and dn='dn1' --设置单片表的库内分表
sharding @@table name='bill' set type='inner' and sharding_func='modlong-6' and sharding_id='sharding_id' and sharding_area='sharding_area' and inner_sharding_id='inner_sharding_id' and inner_sharding_func = 'inner_sharding_func' and dn='dn1,dn2,dn3' --设置分片表的库内分表(dn参数可选)
支持create table和sharding合并为一条sql执行,即通过/* {sharding语句} */
将sharding语句做为hint注释,拼接到create table语句后面。使用示例如下:
CREATE TABLE IF NOT EXISTS `etasd9` ( `employee_id` int NOT NULL, PRIMARY KEY (`employee_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*sharding @@table name='etasd9' set type='sharding' and sharding_algo='PartitionByMod' and sharding_id='employee_id' and dn='asdadada_1,asdadada_2,asdadada_3,asdadada_4,asdadada_5'; */;