這篇文章主要講解了“ShardingSphere5.0.0-alpha如何實現mysql分庫分表”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“ShardingSphere5.0.0-alpha如何實現mysql分庫分表”吧！
本文會基于 Springboot + mybatis + shardingsphere + mysql5.6 + druid 進行實戰講解
本文在上一篇文章[數據分表]的基礎上增加了 分庫 的功能
本文不會介紹 shardingsphere 以及分庫分表的相關概念
本文采用的 shardingsphere 版本是 5.0.0-alpha, 具體見 pom 文件
本文涉及的源碼請參考 分庫
如果看官方文檔時, 請選擇對應的版本 !!!
文中涉及的源碼可能會有誤, 請以上傳到 gitee 的源碼為準.
我們有兩個數據庫 miaosha2 和 miaosha3, 每個數據庫中都有 2 張被拆分過的用戶表 user_info0 和 user_info1
當我們往用戶表插數據時, 會按照一定的規則(根據自增id取模), 落到某個 miaosha 庫中的某張 user_info 表中.
create database miaosha2; DROP TABLE IF EXISTS `miaosha2`.`user_info0`; CREATE TABLE `miaosha2`.`user_info0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_label` varchar(32) COLLATE utf8_bin DEFAULT NULL, `username` varchar(64) COLLATE utf8_bin DEFAULT NULL, `email` varchar(64) COLLATE utf8_bin DEFAULT NULL, `phone` varchar(64) COLLATE utf8_bin DEFAULT NULL, `password` varchar(128) COLLATE utf8_bin NOT NULL, `active` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE = InnoDB AUTO_INCREMENT = 7 DEFAULT CHARSET = utf8 COLLATE = utf8_bin; DROP TABLE IF EXISTS `miaosha2`.`user_info1`; CREATE TABLE `miaosha2`.`user_info1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_label` varchar(32) COLLATE utf8_bin DEFAULT NULL, `username` varchar(64) COLLATE utf8_bin DEFAULT NULL, `email` varchar(64) COLLATE utf8_bin DEFAULT NULL, `phone` varchar(64) COLLATE utf8_bin DEFAULT NULL, `password` varchar(128) COLLATE utf8_bin NOT NULL, `active` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8 COLLATE = utf8_bin; create database miaosha3; DROP TABLE IF EXISTS `miaosha3`.`user_info0`; CREATE TABLE `miaosha3`.`user_info0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_label` varchar(32) COLLATE utf8_bin DEFAULT NULL, `username` varchar(64) COLLATE utf8_bin DEFAULT NULL, `email` varchar(64) COLLATE utf8_bin DEFAULT NULL, `phone` varchar(64) COLLATE utf8_bin DEFAULT NULL, `password` varchar(128) COLLATE utf8_bin NOT NULL, `active` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE = InnoDB AUTO_INCREMENT = 7 DEFAULT CHARSET = utf8 COLLATE = utf8_bin; DROP TABLE IF EXISTS `miaosha3`.`user_info1`; CREATE TABLE `miaosha3`.`user_info1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_label` varchar(32) COLLATE utf8_bin DEFAULT NULL, `username` varchar(64) COLLATE utf8_bin DEFAULT NULL, `email` varchar(64) COLLATE utf8_bin DEFAULT NULL, `phone` varchar(64) COLLATE utf8_bin DEFAULT NULL, `password` varchar(128) COLLATE utf8_bin NOT NULL, `active` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8 COLLATE = utf8_bin;
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.2.RELEASE</version> <relativePath/> </parent> <groupId>com.nimo</groupId> <artifactId>shardingsphere-demo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>shardingsphere-demo</name> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- shardingsphere --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId> <version>5.0.0-alpha</version> </dependency> <!-- 阿里數據源 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.3</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
再次強調下, 本文采用的 shardingsphere 版本是 5.0.0-alpha. 不同版本配置會有差異.
本文在上一篇文章的基礎上增加, 并修改了幾個配置, 下面的源碼中有標記出來
添加了一個數據源配置
添加了一個分庫策略
添加了一個分庫算法
server:
port: 8777
spring:
shardingsphere:
# 展示修改以后的sql語句
props:
sql-show: true
datasource:
# (這里增加了一個 ds1 的數據源)
names: ds0,ds1
common:
type: com.alibaba.druid.pool.DruidDataSource
ds0:
url: jdbc:mysql://127.0.0.1:3306/miaosha2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
username: root
password: '123456'
driver-class-name: com.mysql.cj.jdbc.Driver
# (新增的配置)
ds1:
url: jdbc:mysql://127.0.0.1:3306/miaosha3?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
username: root
password: '123456'
driver-class-name: com.mysql.cj.jdbc.Driver
rules:
sharding:
# 分布式序列算法配置
key-generators:
# 此處必須要配置，否則會導致報錯
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
# 配置 user_info 表
tables:
user_info:
# 分庫策略 (新增的配置)
database-strategy:
standard:
sharding-column: id
sharding-algorithm-name: database-inline
# 配置user_info的分庫分表的規則 (增加了數據源的配置)
actual-data-nodes: ds$->{0..1}.user_info$->{0..1}
# 單分片鍵的標準分片
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: table-inline
# 主鍵id生成策略（雪花算法）
key-generate-strategy:
key-generator-name: snowflake
column: id
# 配置分片算法
sharding-algorithms:
# 通過 id 取模的方式確定數據落到哪個庫 (新增的配置)
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{id % 2}
# 通過 id 取模的方式確定數據落到哪個表
table-inline:
type: INLINE
props:
algorithm-expression: user_info$->{id % 2}
enabled: true
mybatis:
typeAliasesPackage: com.nimo.shardingdatabasedemo.entity
mapperLocations: classpath:mapper/*.xml// sql
<insert id="addUser" parameterType="com.nimo.shardingdemo.entity.UserInfo">
insert into user_info(id, username, password) values (#{id}, #{username}, #{password})
</insert>
// 新增一個用戶信息
@PostMapping("userinfo")
public Object addUserInfo(@RequestBody UserInfo userInfo) {
return userInfoMapper.addUser(userInfo);
}curl -X POST --location "http://localhost:8777/userinfo" \
-H "Content-Type: application/json" \
-d "{
\"username\": \"wangbadan\",
\"password\": \"123456\"
}"感謝各位的閱讀，以上就是“ShardingSphere5.0.0-alpha如何實現mysql分庫分表”的內容了，經過本文的學習后，相信大家對ShardingSphere5.0.0-alpha如何實現mysql分庫分表這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
