正文  软件开发 > html5 >

mycat分库规则sharding-by-intfile(枚举)

mycat分库规则sharding-by-intfile(枚举)1.定义规则rule.xml<tableRule name="sharding-by-intfile-provcode"> <rul...

mycat分库规则sharding-by-intfile(枚举)


1.定义规则rule.xml

  1. <tableRule name="sharding-by-intfile-provcode">
  2. <rule>
  3. <columns>provcode</columns>
  4. <algorithm>hash-int-provcode</algorithm>
  5. </rule>
  6. </tableRule>

  7. <function name="hash-int-provcode"
  8. class="io.mycat.route.function.PartitionByFileMap">
  9. <property name="mapFile">partition-hash-int-provcode.txt</property>
  10. <property name="type">0</property>
  11. </function>
type=0 代表整形
type=1 代表字符串类型

[root@host01 conf]# more partition-hash-int-provcode.txt
1=0
2=1
3=2
4=3
5=4
6=5
7=0
8=1
9=2
10=3
11=4
12=5
DEFAULT_NODE=0 ##找不到省份匹配的情况下,默认放到数据库1
我这里是6个库,序号0-5,将不同的省份映射到对应的库。

2.schema.xml配置文件

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">

  4. <schema name="logdb" checkSQLschema="false" sqlMaxLimit="100">
  5. <table name="tb_log_t" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="mod-long" />
  6. <table name="tb_user_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="sharding-by-intfile-provcode" />
  7. </schema>
  8. <schema name="coss03" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnoss03"/>
  9. <dataNode name="dn1" dataHost="localhost1" database="log01" />
  10. <dataNode name="dn2" dataHost="localhost1" database="log02" />
  11. <dataNode name="dn3" dataHost="localhost1" database="log03" />
  12. <dataNode name="dn4" dataHost="localhost1" database="log04" />
  13. <dataNode name="dn5" dataHost="localhost1" database="log05" />
  14. <dataNode name="dn6" dataHost="localhost1" database="log06" />
  15. <dataNode name="dnoss03" dataHost="localhost1" database="oss03" />
  16. <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  17. writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
  18. <heartbeat>select user()</heartbeat>
  19. <!-- can have multi write hosts -->
  20. <writeHost host="hostM1" url="192.168.56.141:3306" user="root"
  21. password="mysql">
  22. <!-- can have multi read hosts -->
  23. <readHost host="hostS2" url="192.168.56.142:3306" user="root" password="mysql" />
  24. </writeHost>
  25. </dataHost>
  26. </mycat:schema>

3.建表语句
CREATE TABLE `tb_user_t` (
id bigint auto_increment not null primary key,
`name` varchar(64) DEFAULT NULL,
provcode int ,
`createtime` datetime DEFAULT CURRENT_TIMESTAMP,
`moditytime` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
发现分库情况下定义自动增长的id不管用,因为每个库都有自己的自增长id,通过mycat查询的话会有重复的id.
如下:
mysql> select * from tb_user_t order by id;
+----+----------+----------+---------------------+---------------------+
| id | name | provcode | createtime | moditytime |
+----+----------+----------+---------------------+---------------------+
| 1 | name0005 | 5 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |
| 1 | name0001 | 1 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |
| 1 | name0004 | 4 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |
| 1 | name0002 | 2 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |
| 1 | name0003 | 3 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |
| 1 | name0006 | 6 | 2017-08-09 10:54:44 | 2017-08-09 10:54:44 |
| 2 | name0011 | 11 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |
| 2 | name0007 | 7 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |
| 2 | name0010 | 10 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |
| 2 | name0008 | 8 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |
| 2 | name0009 | 9 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |
| 2 | name0012 | 12 | 2017-08-09 10:54:53 | 2017-08-09 10:54:53 |
| 3 | name0013 | 13 | 2017-08-09 11:12:17 | 2017-08-09 11:12:17 |
+----+----------+----------+---------------------+---------------------+
13 rows in set (0.02 sec)

-- The End --





http://www.bkjia.com/HTML5/1223284.htmlwww.bkjia.comtruehttp://www.bkjia.com/HTML5/1223284.htmlTechArticlemycat分库规则sharding-by-intfile(枚举) 1.定义规则rule.xml tableRule name="sharding-by-intfile-provcode" rule columnsprovcode/columns algorithmhash-int-provcode/algorithm...