Mycat sharding rule sharding-by-intfile (enumeration)

Mycat sharding rule sharding-by-intfile (enumeration)

1. Define 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. 
   
8. <Function name = "hash-int-provcode"
   
9. Class = "io. mycat. route. function. PartitionByFileMap">
   
10. <Property name = "mapFile"> partition-hash-int-provcode.txt </property>
    
11. <Property name = "type"> 0 </property>
    
12. </Function>

Type = 0 indicates an integer.
Type = 1 indicates the string type.

[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 # If the Province match cannot be found, it is placed in database 1 by default.
I have 6 databases, numbered 0-5, and map different provinces to the corresponding database.

2. schema. xml configuration file

1. <? Xml version = "1.0"?>
   
2. <! DOCTYPE mycat: schema SYSTEM "schema. dtd">
   
3. <Mycat: schema xmlns: mycat = "http://io.mycat/">
   
4. 
   
5. <Schema name = "logdb" checkSQLschema = "false" sqlMaxLimit = "100">
   
6. <Table name = "tb_log_t" primaryKey = "ID" autoIncrement = "true" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6 "rule =" mod-long "/>
   
7. <Table name = "tb_user_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "sharding-by-intfile-provcode"/>
   
8. </Schema>
   
9. <Schema name = "coss03" checkSQLschema = "false" sqlMaxLimit = "100" dataNode = "dnoss03"/>
   
10. <DataNode name = "dn1" dataHost = "localhost1" database = "log01"/>
    
11. <DataNode name = "dn2" dataHost = "localhost1" database = "log02"/>
    
12. <DataNode name = "dn3" dataHost = "localhost1" database = "log03"/>
    
13. <DataNode name = "dn4" dataHost = "localhost1" database = "log04"/>
    
14. <DataNode name = "dn5" dataHost = "localhost1" database = "log05"/>
    
15. <DataNode name = "dn6" dataHost = "localhost1" database = "log06"/>
    
16. <DataNode name = "dnoss03" dataHost = "localhost1" database = "oss03"/>
    
17. <DataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "0"
    
18. WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1" slaveThreshold = "100">
    
19. <Heartbeat> select user ()
20. <! -- Can have multi write hosts -->
    
21. <WriteHost host = "hostM1" url = "192.168.56.141: 3306" user = "root"
    
22. Password = "mysql">
    
23. <! -- Can have multi read hosts -->
    
24. <ReadHost host = "hostS2" url = "192.168.56.142: 3306" user = "root" password = "mysql"/>
    
25. </WriteHost>
    
26. </DataHost>
    
27. </Mycat: schema>

3. Table creation statement
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;
It is found that the id for automatic growth is not used, because each database has its own self-growth id, and duplicate IDs will exist when querying through mycat.
As follows:
Mysql> select * from tb_user_t order by id;
+ ---- + ---------- + --------------------- +
| Id | name | provcode | createtime | moditytime |
+ ---- + ---------- + --------------------- +
| 1 | name0005 | 5 | 10:54:44 | 10:54:44 |
| 1 | name0001 | 1 | 10:54:44 | 10:54:44 |
| 1 | name0004 | 4 | 10:54:44 | 10:54:44 |
| 1 | name0002 | 2 | 10:54:44 | 10:54:44 |
| 1 | name0003 | 3 | 10:54:44 | 10:54:44 |
| 1 | name0006 | 6 | 10:54:44 | 10:54:44 |
| 2 | name0011 | 11 | 10:54:53 | 10:54:53 |
| 2 | name0007 | 7 | 10:54:53 | 10:54:53 |
| 2 | name0010 | 10 | 10:54:53 | 10:54:53 |
| 2 | name0008 | 8 | 10:54:53 | 10:54:53 |
| 2 | name0009 | 9 | 10:54:53 | 10:54:53 |
| 2 | name0012 | 12 | 10:54:53 | 10:54:53 |
| 3 | name0013 | 13 | 11:12:17 | 11:12:17 |
+ ---- + ---------- + --------------------- +
13 rows in set (0.02 sec)

-- The End --