Mycat2 库 schema

Mycat2支持使用SQL直接建表,请看Mycat2入门,可以通过建立SQL执行脚本,在客户端执行即可.

配置的schema的逻辑库逻辑表必须在原型库(prototype)中有对应的物理库物理表,否则不能启动

库配置

{库名}.schema.json保存在schemas文件夹 库配置

{
  "customTables": {},
  "globalTables": {},
  "normalTables": {},
  "schemaName": "test",
  "shardingTables": {},
  "targetName": "prototype"
}

targetName自动从prototype目标加载test库下的物理表或者视图作为单表,prototype必须是mysql服务器

该配置对应1.6的schema上配置dataNode用于读写分离

单表配置

1.18前

{
  "schemaName": "mysql-test",
  "normalTables": {
    "role_edges": {
      "createTableSQL":null,//可选
      "dataNode": {
        "schemaName": "mysql",//物理库
        "tableName": "role_edges",//物理表
        "targetName": "prototype"//指向集群,或者数据源
      }
    }
    ......

1.18后

{
  "schemaName": "mysql-test",
  "normalTables": {
    "role_edges": {
      "createTableSQL":null,//可选
      "locality": {
        "schemaName": "mysql",//物理库
        "tableName": "role_edges",//物理表
        "targetName": "prototype"//指向集群,或者数据源
      }
    }
    ......

全局表配置

1.18前

{
  "schemaName": "mysql-test",
  "globalTables": {
    "role_edges": {
      "dataNodes": [{"targetName": "c0"},{"targetName": "c1"}]
    }
    ......

1.18后

{
  "schemaName": "mysql-test",
  "globalTables": {
    "role_edges": {
      "broadcast": [{"targetName": "c0"},{"targetName": "c1"}]
    }
    ......

分片表配置

{
  "customTables": {},
  "globalTables": {},
  "normalTables": {},
  "schemaName": "db1",
  "shardingTables": {
    "travelrecord": {
      "function": {
        "properties": {
          "dbNum": "2",//分库数量
          "tableNum": "2",//分表数量
          "tableMethod": "hash(id)",//分表分片函数
          "storeNum": 2,//实际存储节点数量
          "dbMethod": "hash(id)"//分库分片函数
        }
      }
    }
  }
}

上述配置自动使用c0,c1两个集群作为存储节点

分片表配置-hash型自动分片算法

{
  "customTables": {},
  "globalTables": {},
  "normalTables": {},
  "schemaName": "db1",
  "shadringTables": {
    "travelrecord": {
      "function": {
        "properties": {
          "dbNum": "2",//分库数量
          "tableNum": "2",//分表数量
          "tableMethod": "hash(id)",//分表分片函数
          "storeNum": 2,//实际存储节点数量
          "dbMethod": "hash(id)",//分库分片函数
          "mappingFormat": "c${targetIndex}/db1_${dbIndex}/travelrecord_${tableIndex}"
        }
      }
    }
  }
}

targetIndex,dbIndex,tableIndex总是从0开始计算,支持groovy运算生成目标名,库名,表名

分片表配置-自定义分片算法

1.18前

{
  "customTables": {},
  "globalTables": {},
  "normalTables": {},
  "schemaName": "db1",
  "shadingTables": {
    "travelrecord": {
      "function": {
         "clazz": ,//具体自定义分片算法
        "properties": {
          ...分片算法参数
        }
      },
     "dataNode":{
         "targetNames":"c$0-1",
         "schemaNames":"db1_$0-1",
         "tableNames":"t1_$0-1"
     }
    }
  }
}

1.18后

{
  "customTables": {},
  "globalTables": {},
  "normalTables": {},
  "schemaName": "db1",
  "shadingTables": {
    "travelrecord": {
      "function": {
        "clazz": ,//具体自定义分片算法
        "properties": {


          ...分片算法参数
        }
      },
     "partition":{
         "targetNames":"c$0-1",
         "schemaNames":"db1_$0-1",
         "tableNames":"t1_$0-1"
     }
    }
  }
}

partition配置存储节点,在分片算法无法使用的时候就扫描这些存储节点,所以分片算法无法正确配置的时候仍然可以查询,但是可能插入报错 需要注意的是在此处

partition中的生成表达式不支持groovy运算只支持$0-1语法生成

partition中的targetName-schemaName-tableName不能重复

样例

{
  "customTables":{},
  "globalTables":{},
  "normalTables":{},
  "schemaName":"db1",
  "shadingTables":{
    "sharding":{
      "createTableSQL":"CREATE TABLE db1.`sharding` (\n  `id` bigint NOT NULL AUTO_INCREMENT,\n  `user_id` varchar(100) DEFAULT NULL,\n  `create_time` date DEFAULT NULL,\n  `fee` decimal(10,0) DEFAULT NULL,\n  `days` int DEFAULT NULL,\n  `blob` longblob,\n  PRIMARY KEY (`id`),\n  KEY `id` (`id`)\n) ENGINE=InnoDB  DEFAULT CHARSET=utf8",
      "function":{
        "clazz":"io.mycat.router.mycat1xfunction.PartitionByHotDate",
        "properties":{
          "lastTime":90,
          "partionDay":180,
          "dateFormat":"yyyy-MM-dd",
          "columnName":"create_time"
        },
        "ranges":{}
      },
      "partition":{
        "schemaNames":"db1",
        "tableNames":"sharding_$0-1",
        "targetNames":"c0"
      }
    }
  }
}

for (String target : targets) {   
    for (String schema : schemas) {    
        for (String table : tables) { 
            ....生成存储节点
        }
    }
}