Mycat2 庫 schema

Mycat2支持使用SQL直接建表,請看Mycat2入門,可以通過建立SQL執(zhí)行腳本,在客戶端執(zhí)行即可.

配置的schema的邏輯庫邏輯表必須在原型庫(prototype)中有對應(yīng)的物理庫物理表,否則不能啟動

庫配置

{庫名}.schema.json保存在schemas文件夾 庫配置

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

targetName自動從prototype目標(biāo)加載test庫下的物理表或者視圖作為單表,prototype必須是mysql服務(wù)器

該配置對應(yīng)1.6的schema上配置dataNode用于讀寫分離

單表配置

1.18前

{
  "schemaName": "mysql-test",
  "normalTables": {
    "role_edges": {
      "createTableSQL":null,//可選
      "dataNode": {
        "schemaName": "mysql",//物理庫
        "tableName": "role_edges",//物理表
        "targetName": "prototype"http://指向集群,或者數(shù)據(jù)源
      }
    }
    ......

1.18后

{
  "schemaName": "mysql-test",
  "normalTables": {
    "role_edges": {
      "createTableSQL":null,//可選
      "locality": {
        "schemaName": "mysql",//物理庫
        "tableName": "role_edges",//物理表
        "targetName": "prototype"http://指向集群,或者數(shù)據(jù)源
      }
    }
    ......

全局表配置

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",//分庫數(shù)量
          "tableNum": "2",//分表數(shù)量
          "tableMethod": "hash(id)",//分表分片函數(shù)
          "storeNum": 2,//實際存儲節(jié)點數(shù)量
          "dbMethod": "hash(id)"http://分庫分片函數(shù)
        }
      }
    }
  }
}

上述配置自動使用c0,c1兩個集群作為存儲節(jié)點

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

{
  "customTables": {},
  "globalTables": {},
  "normalTables": {},
  "schemaName": "db1",
  "shadringTables": {
    "travelrecord": {
      "function": {
        "properties": {
          "dbNum": "2",//分庫數(shù)量
          "tableNum": "2",//分表數(shù)量
          "tableMethod": "hash(id)",//分表分片函數(shù)
          "storeNum": 2,//實際存儲節(jié)點數(shù)量
          "dbMethod": "hash(id)",//分庫分片函數(shù)
          "mappingFormat": "c${targetIndex}/db1_${dbIndex}/travelrecord_${tableIndex}"
        }
      }
    }
  }
}

targetIndex,dbIndex,tableIndex總是從0開始計算,支持groovy運算生成目標(biāo)名,庫名,表名

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

1.18前

{
  "customTables": {},
  "globalTables": {},
  "normalTables": {},
  "schemaName": "db1",
  "shadingTables": {
    "travelrecord": {
      "function": {
         "clazz": ,//具體自定義分片算法
        "properties": {
          ...分片算法參數(shù)
        }
      },
     "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": {


          ...分片算法參數(shù)
        }
      },
     "partition":{
         "targetNames":"c$0-1",
         "schemaNames":"db1_$0-1",
         "tableNames":"t1_$0-1"
     }
    }
  }
}

partition配置存儲節(jié)點,在分片算法無法使用的時候就掃描這些存儲節(jié)點,所以分片算法無法正確配置的時候仍然可以查詢,但是可能插入報錯 需要注意的是在此處

partition中的生成表達(dá)式不支持groovy運算只支持$0-1語法生成

partition中的targetName-schemaName-tableName不能重復(fù)

樣例

{
  "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) { 
            ....生成存儲節(jié)點
        }
    }
}