一、后台配置文件
# 连接地址 url: jdbc:mysql://127.0.0.1:8066/CHUNK?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true # 用户名 username: root # 密码 password: bluetooth@wy2019
8066:mycat默认端口
root:mycat配置文件设置、
password:mycat配置文件配置
二、mycat配置
2.1 schema.xml配置
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- <schema>:表示的是在mycat中的逻辑库配置,逻辑库名称为:CHUNK --> <schema name="CHUNK" checkSQLschema="false" sqlMaxLimit="10000"> <!-- <table>:表示在mycat中的逻辑表配置,逻辑表名称为:cn_chunk,映射到两个数据库节点dataNode中,设置几个几点代表要分片几个库,切分规则为:rule1(在rule.xml配置) --> <table name="cn_chunk" primaryKey="id" autoIncrement="true" dataNode="dn01,dn02,dn03" rule="rule1"> <childTable name="cn_chunk_lockset" primaryKey="id" joinKey="chunkId" parentKey="id" /></table> </schema> <!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost --> <!-- <dataNode>:表示数据库节点,这个节点不一定是单节点,可以配置成读写分离. --> <!-- <dataHost>:真实的数据库的地址配置.在下面进行配置 --> <!-- <heartbeat>:用户心跳检测 --> <!-- <writeHost>:写库的配置 --> <dataNode name="dn01" dataHost="localhost" database="chunk001" /> <dataNode name="dn02" dataHost="localhost" database="chunk002" /> <dataNode name="dn03" dataHost="localhost" database="chunk003" /> <!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 --> <dataHost name="localhost" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <!-- 连接到本地数据库服务的配置 --> <writeHost host="server1" url="127.0.0.1:3306" user="root" password="root"/> </dataHost> </mycat:schema>
2.2 rule.xml配置
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mycat:rule SYSTEM "rule.dtd"> <mycat:rule xmlns:mycat="http://io.mycat/"> <tableRule name="rule1"> <rule> <!-- 是按照id列进行切分,切分规则是采取取模的方式, --> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- <property name="count">2</property>:这里配置了我们有拆分了多个库(表),需要和前面配置 <table name="user" primaryKey="id" dataNode="dn01,dn02,dn03" rule="rule1" /> 中的dataNode个数一致,否则会出错. --> <property name="count">3</property> </function> </mycat:rule>
2.3 server.xml配置
<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 --> <property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 --> <property name="sequnceHandlerType">1</property> <!-- <property name="sequnceHandlerType">2</property>--> <!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议--> <!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号--> <!-- <property name="processorBufferChunk">40960</property> --> <!-- <property name="processors">1</property> <property name="processorExecutor">32</property> --> <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena--> <property name="processorBufferPoolType">0</property> <!--默认是65535 64K 用于sql解析时最大文本长度 --> <!--<property name="maxStringLiteralLength">65535</property>--> <!--<property name="sequnceHandlerType">0</property>--> <!--<property name="backSocketNoDelay">1</property>--> <!--<property name="frontSocketNoDelay">1</property>--> <!--<property name="processorExecutor">16</property>--> <!-- <property name="serverPort">8066</property> <property name="managerPort">9066</property> <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> --> <!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志--> <property name="handleDistributedTransactions">0</property> <!-- off heap for merge/order/group/limit 1开启 0关闭 --> <property name="useOffHeapForMerge">1</property> <!-- 单位为m --> <property name="memoryPageSize">1m</property> <!-- 单位为k --> <property name="spillsFileBufferSize">1k</property> <property name="useStreamOutput">0</property> <!-- 单位为m --> <property name="systemReserveMemorySize">384m</property> <!--是否采用zookeeper协调切换 --> <property name="useZKSwitch">true</property> </system> <!-- 全局SQL防火墙设置 --> <!-- <firewall> <whitehost> <host host="127.0.0.1" user="mycat"/> <host host="127.0.0.2" user="mycat"/> </whitehost> <blacklist check="false"> </blacklist> </firewall> --> <user name="root"> <!-- java项目配置文件连接的密码 --> <property name="password">bluetooth@wy2019</property> <!-- schema.xml下的逻辑库,要一致 --> <property name="schemas">CHUNK</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> <user name="user"> <property name="password">user</property> <!-- schema.xml下的逻辑库,要一致 --> <property name="schemas">CHUNK</property> <property name="readOnly">true</property> </user> </mycat:server>
2.4 sequence_db_conf.properties配置
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
#逻辑库的分片函数在对应个库下
CN_CHUNK=dn01
三、建表语句
#创建表语句区块与子表区块锁
#添加表 区块
DROP TABLE IF EXISTS `cn_chunk`;
CREATE TABLE `cn_chunk` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`createdAt` datetime DEFAULT NULL COMMENT '创建时间',
`modifiedAt` datetime DEFAULT NULL COMMENT '更新时间',
`createdBy` varchar(36) DEFAULT NULL COMMENT '创建人',
`modifiedBy` varchar(36) DEFAULT NULL COMMENT '更新人',
`version` int(11) DEFAULT NULL COMMENT '版本号',
`isDelete` int(11) DEFAULT NULL COMMENT '是否删除',
`keyInfoId` varchar(36) DEFAULT NULL COMMENT '钥匙id',
`chunkOne` int(11) DEFAULT NULL COMMENT '区块1',
`chunkTwo` int(11) DEFAULT NULL COMMENT '区块2',
`chunkThree` int(11) DEFAULT NULL COMMENT '区块3',
`chunkFour` int(11) DEFAULT NULL COMMENT '区块4',
`chunkFive` int(11) DEFAULT NULL COMMENT '区块5',
`chunkSix` int(11) DEFAULT NULL COMMENT '区块6',
`chunkSeven` int(11) DEFAULT NULL COMMENT '区块7',
`chunkEight` int(11) DEFAULT NULL COMMENT '区块8',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#添加表 区块锁
DROP TABLE IF EXISTS `cn_chunk_lockset`;
CREATE TABLE `cn_chunk_lockset` (
`id` varchar(36) NOT NULL,
`createdAt` datetime DEFAULT NULL COMMENT '创建时间',
`modifiedAt` datetime DEFAULT NULL COMMENT '更新时间',
`createdBy` varchar(36) DEFAULT NULL COMMENT '创建人',
`modifiedBy` varchar(36) DEFAULT NULL COMMENT '更新人',
`version` int(11) DEFAULT NULL COMMENT '版本号',
`isDelete` int(11) DEFAULT NULL COMMENT '是否删除',
`taskId` varchar(36) DEFAULT NULL COMMENT '任务id',
`keyInfoId` varchar(36) DEFAULT NULL COMMENT '钥匙id',
`startTime` datetime DEFAULT NULL COMMENT '起始时间',
`endTime` datetime DEFAULT NULL COMMENT '截止时间',
`chunkLocation` int(11) DEFAULT NULL COMMENT '对应区块(1-8)',
`lockSetId` varchar(36) DEFAULT NULL COMMENT '锁具信息id',
`lockId` varchar(50) DEFAULT NULL COMMENT '锁具Id',
`lockSetName` varchar(50) DEFAULT NULL COMMENT '锁具名称',
`principalId` varchar(36) DEFAULT NULL COMMENT '执行人id',
`lockIdDecimal` int(11) DEFAULT NULL COMMENT '锁具Id十进制',
`chunkId` bigint(20) DEFAULT NULL COMMENT '区块id',
`thisUpdate` int(11) DEFAULT NULL COMMENT '本次更新(0:否,1:是)',
`sequence` int(11) DEFAULT NULL COMMENT '区块锁序列号',
PRIMARY KEY (`id`),
KEY `fk_cn_chunk_lockset_cn_chunk` (`chunkId`),
CONSTRAINT `cn_chunk_lockset_ibfk_1` FOREIGN KEY (`chunkId`) REFERENCES `cn_chunk` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
四、对应实体类
@Getter @Setter @Entity @Table(name = "cn_chunk") public class Chunk extends ChunkBaseEntity { /** 钥匙id*/ @ApiModelProperty(value = "钥匙id") private String keyInfoId; /** 区块1*/ @ApiModelProperty(value = "区块1") private Integer chunkOne = 0; /** 区块2*/ @ApiModelProperty(value = "区块2") private Integer chunkTwo = 0; /** 区块3*/ @ApiModelProperty(value = "区块3") private Integer chunkThree = 0; /** 区块4*/ @ApiModelProperty(value = "区块4") private Integer chunkFour = 0; /** 区块5*/ @ApiModelProperty(value = "区块5") private Integer chunkFive = 0; /** 区块6*/ @ApiModelProperty(value = "区块6") private Integer chunkSix = 0; /** 区块7*/ @ApiModelProperty(value = "区块7") private Integer chunkSeven = 0; /** 区块8*/ @ApiModelProperty(value = "区块8") private Integer chunkEight = 0; }
@Getter @Setter @Entity @Table(name = "cn_chunk_lockset") public class ChunkLockSet extends BaseEntity { /** 任务id*/ @ApiModelProperty(value = "任务id") @Column(length = 36) private String taskId; /** 钥匙id*/ @ApiModelProperty(value = "钥匙id") private String keyInfoId; /** 起始时间*/ @ApiModelProperty(value = "起始时间") private LocalDateTime startTime; /** 截止时间*/ @ApiModelProperty(value = "截止时间") private LocalDateTime endTime; /** 区块*/ @ApiModelProperty(value = "区块") @ManyToOne @JoinColumn(name = "chunkId", updatable = false, insertable = false) @JsonIgnore private Chunk chunk; /** 区块id*/ @ApiModelProperty(value = "区块id") @Column(name = "chunkId", updatable = false, length = 36) private Long chunkId; /** 对应区块(1-8)*/ @ApiModelProperty(value = "对应区块(1-8)") private Integer chunkLocation; /** 锁具信息id*/ @ApiModelProperty(value = "锁具信息id") @Column(name = "lockSetId", length = 36) private String lockSetId; /** 锁具Id*/ @ApiModelProperty(value = "锁具Id") @Column(length = 50) private String lockId; /** 锁具Id十进制*/ @ApiModelProperty(value = "锁具Id十进制") private Integer lockIdDecimal; /** 锁具名称*/ @ApiModelProperty(value = "锁具名称") @Column(length = 50) private String lockSetName; /** 执行人id*/ @ApiModelProperty(value = "执行人id") @Column(name = "principalId", length = 36) private String principalId; /** 是否已上传(0:否,1:是)*/ @ApiModelProperty(value = "是否已上传(0:否,1:是)") private Integer thisUpdate = BooleanEnum.FALSE.ordinal(); /** 区块锁序列号*/ @ApiModelProperty(value = "区块锁序列号") private Integer sequence; }
@Getter @Setter @MappedSuperclass @EntityListeners(AuditingEntityListener.class) @Slf4j public abstract class ChunkBaseEntity implements Serializable { /** * 主键id * GenericGenerator 注解 strategy 参数设置参考如下 * @see org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory * 参考网站:https://blog.csdn.net/mafian/article/details/53968878 */ @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id", updatable = false) @ApiModelProperty(value = "主键id", position = 1) private Long id; /** 创建时间*/ @CreatedDate @ApiModelProperty(value = "创建时间", position = 2) @JsonFormat @DateTimeFormat private LocalDateTime createdAt; /** 更新时间*/ @LastModifiedDate @ApiModelProperty(value = "更新时间", position = 3) @JsonFormat @DateTimeFormat private LocalDateTime modifiedAt; /** 创建人*/ @Column(length = 36) @CreatedBy @ApiModelProperty(value = "创建人", position = 4) private String createdBy; /** 更新人*/ @Column(length = 36) @LastModifiedBy @ApiModelProperty(value = "更新人", position = 5) private String modifiedBy; /** 版本(乐观锁)*/ @Version @ApiModelProperty(value = "版本(乐观锁)", position = 6) private Integer version; /** 逻辑删除*/ @ApiModelProperty(value = "逻辑删除", position = 7) private Integer isDelete = BooleanEnum.FALSE.ordinal(); }
五、mycat分片函数
配置文件:sequence_db_conf.properties
设置节点:CN_CHUNK=dn01
该参数设置在哪个节点,则就在对应的数据库内执行
#函数自增 实现新增数据自增长以1开始 #添加表 DROP TABLE IF EXISTS MYCAT_SEQUENCE; CREATE TABLE MYCAT_SEQUENCE ( NAME VARCHAR (50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 100, PRIMARY KEY (NAME) ) ENGINE = INNODB ; INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 10000000, 100); DROP FUNCTION IF EXISTS `mycat_seq_currval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN DECLARE retval VARCHAR(64); SET retval="-999999999,null"; SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name; RETURN retval ; END ;; DELIMITER ; DROP FUNCTION IF EXISTS `mycat_seq_nextval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END ;; DELIMITER ; DROP FUNCTION IF EXISTS `mycat_seq_setval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END ;; DELIMITER ; #插入一条数据,这条数据是我们这个表所需要的。 #必须是大写的字符,不然就会报错 #插入需要自增张的表的策略 #其中cn_chunk代表哪张表实现自增 insert into MYCAT_SEQUENCE (name,current_value,increment) values ('cn_chunk',0,1);
六、关闭默认事务
import org.springframework.context.annotation.Configuration; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; /** * 关闭jpamor默认事务 * @author lixin.shao */ @EnableJpaRepositories(basePackages = "com.hs.lock.bluetooth.chunk", enableDefaultTransactions = false) @Configuration public class JpaTransactionConfig { }