Sharding-JDBC基本使用，整合Springboot实现分库分表，读写分离 (2)

3、application.properties文件

spring.main.allow-bean-definition-overriding=true mybatis-plus.mapper-locations= classpath:/mapper/*.xml mybatis-plus.configuration.log-impl= org.apache.ibatis.logging.stdout.StdOutImpl

4、分库分表实现

 4.1、先说下数据源，结合之前mysql主从的文章，我本地127.0.0.1:3307端口是主，127.0.0.1:3308端口是从。

   在3307下建立两个库app1和app2，同时每个库里面建立两张表user1和user2表，用来完成分库分表。

   下面是app1库SQL语句：

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user1 -- ---------------------------- DROP TABLE IF EXISTS `user1`; CREATE TABLE `user1` ( `id` bigint(11) NOT NULL COMMENT '主键id', `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for user2 -- ---------------------------- DROP TABLE IF EXISTS `user2`; CREATE TABLE `user2` ( `id` bigint(11) NOT NULL COMMENT '主键id', `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC; SET FOREIGN_KEY_CHECKS = 1;

   下面是app2库SQL语句：

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user1 -- ---------------------------- DROP TABLE IF EXISTS `user1`; CREATE TABLE `user1` ( `id` bigint(11) NOT NULL COMMENT '主键id', `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for user2 -- ---------------------------- DROP TABLE IF EXISTS `user2`; CREATE TABLE `user2` ( `id` bigint(11) NOT NULL COMMENT '主键id', `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;

 4.2、这里我们解释一下配置的分库分表规则实现将数据插入到app1和app2库，user1和user2表

sharding: default-database-strategy: inline: sharding-column: id #分片的字段是id主键 algorithm-expression: app$->{(id % 2)+1} #分片的算法是 id对2求余然后加1 tables: user: actual-data-nodes: app$->{1..2}.user$->{1..2} #实际的数据节点是(app1/app2).(user1/user2) table-strategy: inline: sharding-column: id #分表的分片字段是主键id algorithm-expression: user$->{((""+id)[2..10].toInteger() % 2)+1} #分表的算法是取id的2-10位对2求余然后加1 key-generator: column: id # 自动生成主键 type: SNOWFLAKE # 生成主键的规则是雪花算法