SpringCloud微服務(wù)實(shí)戰(zhàn)——搭建企業(yè)級開發(fā)框架（二十七）：集成多數(shù)據(jù)源+Seata分布式事務(wù)+讀寫分離+分庫分表

??讀寫分離：為了確保數(shù)據(jù)庫產(chǎn)品的穩(wěn)定性，很多數(shù)據(jù)庫擁有雙機(jī)熱備功能。也就是，第一臺數(shù)據(jù)庫服務(wù)器，是對外提供增刪改業(yè)務(wù)的生產(chǎn)服務(wù)器；第二臺數(shù)據(jù)庫服務(wù)器，主要進(jìn)行讀的操作。
??目前有多種方式實(shí)現(xiàn)讀寫分離，一種是Mycat這種數(shù)據(jù)庫中間件，需要單獨(dú)部署服務(wù)，通過配置來實(shí)現(xiàn)讀寫分離，不侵入到業(yè)務(wù)代碼中；還有一種是dynamic-datasource/shardingsphere-jdbc這種，需要在業(yè)務(wù)代碼引入jar包進(jìn)行開發(fā)。
??本框架集成 dynamic-datasource（多數(shù)據(jù)源+讀寫分離+分庫）+ druid（數(shù)據(jù)庫連接池）+ seata（分布式事務(wù)）+ mybatis-plus+shardingsphere-jdbc(分庫分表)， dynamic-datasource可以實(shí)現(xiàn)簡單的分庫操作，目前還不支持分表，復(fù)雜的分庫分表需要用到shardingsphere-jdbc,本文參考dynamic-datasource中的實(shí)例，模擬用戶下單，扣商品庫存，扣用戶余額操作，初步可分為訂單服務(wù)+商品服務(wù)+用戶服務(wù)。

一、Seata安裝配置

1、我們將服務(wù)安裝到CentOS環(huán)境上，所以這里我們下載tar.gz版本,下載地址:https://github.com/seata/seata/releases

seata-server-1.4.1.tar.gz

2、上傳到CentOS服務(wù)器，執(zhí)行解壓命令

tar -zxvf seata-server-1.4.1.tar.gz

3、下載Seata需要的SQL腳本，新建Seata數(shù)據(jù)庫并將需要使用的數(shù)據(jù)庫腳本seata-1.4.1\seata-1.4.1\script\server\db\mysql.sql刷進(jìn)去


seata數(shù)據(jù)庫

4、修改Seata配置文件，將seata服務(wù)端的注冊中心和配置中心設(shè)置為Nacos

vi /bigdata/soft_home/seata/conf/registry.conf

registry {
  # file 、nacos 、eureka、redis、zk、consul、etcd3、sofa
  type = "nacos"
  loadBalance = "RandomLoadBalance"
  loadBalanceVirtualNodes = 10

  nacos {
    application = "seata-server"
    serverAddr = "127.0.0.1:8848"
    group = "SEATA_GROUP"
    namespace = ""
    cluster = "default"
    username = "nacos"
    password = "nacos"
  }
  eureka {
    serviceUrl = "http://localhost:8761/eureka"
    application = "default"
    weight = "1"
  }
  redis {
    serverAddr = "localhost:6379"
    db = 0
    password = ""
    cluster = "default"
    timeout = 0
  }
  zk {
    cluster = "default"
    serverAddr = "127.0.0.1:2181"
    sessionTimeout = 6000
    connectTimeout = 2000
    username = ""
    password = ""
  }
  consul {
    cluster = "default"
    serverAddr = "127.0.0.1:8500"
  }
  etcd3 {
    cluster = "default"
    serverAddr = "http://localhost:2379"
  }
  sofa {
    serverAddr = "127.0.0.1:9603"
    application = "default"
    region = "DEFAULT_ZONE"
    datacenter = "DefaultDataCenter"
    cluster = "default"
    group = "SEATA_GROUP"
    addressWaitTime = "3000"
  }
  file {
    name = "file.conf"
  }
}

config {
  # file、nacos 、apollo、zk、consul、etcd3
  type = "nacos"

  nacos {
    serverAddr = "127.0.0.1:8848"
    namespace = ""
    group = "SEATA_GROUP"
    username = "nacos"
    password = "nacos"
  }
  consul {
    serverAddr = "127.0.0.1:8500"
  }
  apollo {
    appId = "seata-server"
    apolloMeta = "http://192.168.1.204:8801"
    namespace = "application"
    apolloAccesskeySecret = ""
  }
  zk {
    serverAddr = "127.0.0.1:2181"
    sessionTimeout = 6000
    connectTimeout = 2000
    username = ""
    password = ""
  }
  etcd3 {
    serverAddr = "http://localhost:2379"
  }
  file {
    name = "file.conf"
  }
}

5、在Nacos添加Seata配置文件，修改script/config-center/config.txt，將script目錄上傳到CentOS服務(wù)器，執(zhí)行script/config-center/nacos/nacos-config.sh命令

service.vgroupMapping.gitegg_seata_tx_group=default

service.default.grouplist=127.0.0.1:8091

store.mode=db

store.db.url=jdbc:mysql://127.0.0.1:3306/seata?useUnicode=true
store.db.user=root
store.db.password=root

chmod 777 nacos-config.sh

sh nacos-config.sh -h 127.0.0.1 -p 8848
設(shè)置成功

6、在CentOS上進(jìn)去到Seata安裝目錄的bin目錄執(zhí)行命令，啟動Seata服務(wù)端

nohup ./seata-server.sh -h 127.0.0.1 -p 8091 >log.out 2>1 &
如果服務(wù)器有多網(wǎng)卡，存在多個(gè)ip地址，-h后面一定要加可以訪問的ip地址

7、在Nacos上可以看到配置文件和服務(wù)已經(jīng)注冊成功


配置

服務(wù)

二、Seata安裝成功后，我們需要在微服務(wù)中集成Seata客戶端

1、因?yàn)槲覀冊谖⒎?wù)中使用Seata，所以，我們將Seata客戶端的依賴添加在gitegg-plaform-cloud中

        <!-- Seata 分布式事務(wù)管理 -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-seata</artifactId>
        </dependency>

2、我們這里打算使用多數(shù)據(jù)源，所以這里也把動態(tài)多數(shù)據(jù)源組件Dynamic Datasource加入到gitegg-plaform-mybatis依賴中

        <!-- 動態(tài)數(shù)據(jù)源 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
        </dependency>

3、配置Nacos數(shù)據(jù)庫多數(shù)據(jù)源及Seata

spring:
  datasource: 
    druid:
      stat-view-servlet:
        enabled: true
        loginUsername: admin
        loginPassword: 123456
    dynamic:
      # 設(shè)置默認(rèn)的數(shù)據(jù)源或者數(shù)據(jù)源組,默認(rèn)值即為master
      primary: master
      # 設(shè)置嚴(yán)格模式,默認(rèn)false不啟動. 啟動后在未匹配到指定數(shù)據(jù)源時(shí)候會拋出異常,不啟動則使用默認(rèn)數(shù)據(jù)源.
      strict: false
      # 開啟seata代理，開啟后默認(rèn)每個(gè)數(shù)據(jù)源都代理，如果某個(gè)不需要代理可單獨(dú)關(guān)閉
      seata: true
      #支持XA及AT模式,默認(rèn)AT
      seata-mode: AT
      druid:
        initialSize: 1
        minIdle: 3
        maxActive: 20
        # 配置獲取連接等待超時(shí)的時(shí)間
        maxWait: 60000
        # 配置間隔多久才進(jìn)行一次檢測，檢測需要關(guān)閉的空閑連接，單位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一個(gè)連接在池中最小生存的時(shí)間，單位是毫秒
        minEvictableIdleTimeMillis: 30000
        validationQuery: select 'x'
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打開PSCache，并且指定每個(gè)連接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置監(jiān)控統(tǒng)計(jì)攔截的filters，去掉后監(jiān)控界面sql無法統(tǒng)計(jì)，'wall'用于防火墻
        filters: config,stat,slf4j
        # 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000;
        # 合并多個(gè)DruidDataSource的監(jiān)控?cái)?shù)據(jù)
        useGlobalDataSourceStat: true
      datasource: 
        master: 
          url: jdbc:mysql://127.0.0.1/gitegg_cloud?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&all owMultiQueries=true&serverTimezone=Asia/Shanghai
          username: root
          password: root
        mall_user:
          url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_user?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
          username: root
          password: root
        mall_goods:
          url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_goods?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
          username: root
          password: root
        mall_order:
          url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_order?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
          username: root
          password: root
        mall_pay:
          url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_pay?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&alowMultiQueries=true&serverTimezone=Asia/Shanghai
          username: root
          password: root
seata:
  enabled: true
  application-id: ${spring.application.name}
  tx-service-group: gitegg_seata_tx_group
  # 一定要是false
  enable-auto-data-source-proxy: false
  service:
    vgroup-mapping:
      #key與上面的gitegg_seata_tx_group的值對應(yīng)
      gitegg_seata_tx_group: default
  config:
    type: nacos
    nacos:
      namespace:
      serverAddr: 127.0.0.1:8848
      group: SEATA_GROUP
      userName: "nacos"
      password: "nacos"
  registry:
    type: nacos
    nacos:
      #seata服務(wù)端(TC)在nacos中的應(yīng)用名稱
      application: seata-server
      server-addr: 127.0.0.1:8848
      namespace:
      userName: "nacos"
      password: "nacos"

三、數(shù)據(jù)庫表設(shè)計(jì)

? ? 這里參考Dynamic Datasource官方提供的示例項(xiàng)目,并結(jié)合電商項(xiàng)目數(shù)據(jù)庫設(shè)計(jì),新建四個(gè)數(shù)據(jù)庫，gitegg_cloud_mall_goods（商品數(shù)據(jù)庫），gitegg_cloud_mall_order（訂單數(shù)據(jù)庫），gitegg_cloud_mall_pay（支付數(shù)據(jù)庫）,gitegg_cloud_mall_user（賬戶數(shù)據(jù)庫）四個(gè)數(shù)據(jù)庫，下面是具體表結(jié)構(gòu)和簡要說明:

1、商品數(shù)據(jù)庫表設(shè)計(jì)

表設(shè)計(jì)：

  • 商品分類表：t_mall_goods_category
  • 商品品牌表： t_mall_goods_brand
  • 分類品牌關(guān)聯(lián)關(guān)系表：t_mall_goods_category_brand
  • 商品規(guī)格參數(shù)組表： t_mall_goods_spec_group
  • 商品規(guī)格參數(shù)表：t_mall_goods_spec_param
  • 商品SPU表： t_mall_goods_spu
  • 商品SPU詳情表： t_mall_goods_spu_detail
  • 商品SKU表： t_mall_goods_sku

關(guān)系：

  • 一個(gè)分類有多個(gè)品牌，一個(gè)品牌屬于多個(gè)分類，所以是多對多
  • 一個(gè)分類有多個(gè)規(guī)格組，一個(gè)規(guī)格組有多個(gè)規(guī)格參數(shù)，所以是一對多
  • 一個(gè)分類下有多個(gè)SPU，所以是一對多
  • 一個(gè)品牌下有多個(gè)SPU，所以是一對多
  • 一個(gè)SPU下有多個(gè)SKU，所以是一對多
DROP TABLE IF EXISTS `t_mall_goods_brand`;
CREATE TABLE `t_mall_goods_brand`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '品牌id',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '品牌名稱',
  `image` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '品牌圖片地址',
  `letter` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '品牌的首字母',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '品牌表，一個(gè)品牌下有多個(gè)商品（spu），一對多關(guān)系' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_goods_brand_category
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_goods_brand_category`;
CREATE TABLE `t_mall_goods_brand_category`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '品牌id',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `brand_id` bigint(20) NOT NULL COMMENT '品牌id',
  `category_id` bigint(20) NOT NULL COMMENT '商品類目id',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `key_tenant_id`(`tenant_id`) USING BTREE,
  INDEX `key_category_id`(`category_id`) USING BTREE,
  INDEX `key_brand_id`(`brand_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品分類和品牌的中間表，兩者是多對多關(guān)系' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_goods_category
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_goods_category`;
CREATE TABLE `t_mall_goods_category`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '類目id',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '類目名稱',
  `parent_id` bigint(20) NOT NULL COMMENT '父類目id,頂級類目填0',
  `is_parent` tinyint(2) NOT NULL COMMENT '是否為父節(jié)點(diǎn)，0為否，1為是',
  `sort` tinyint(2) NOT NULL COMMENT '排序指數(shù)，越小越靠前',
  `comments` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '備注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `key_parent_id`(`parent_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品類目表，類目和商品(spu)是一對多關(guān)系，類目與品牌是多對多關(guān)系' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_goods_sku
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_goods_sku`;
CREATE TABLE `t_mall_goods_sku`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `spu_id` bigint(20) NOT NULL COMMENT 'spu id',
  `title` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品標(biāo)題',
  `images` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '商品的圖片，多個(gè)圖片以‘,’分割',
  `stock` int(8) UNSIGNED NULL DEFAULT 0 COMMENT '庫存',
  `price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '銷售價(jià)格',
  `indexes` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '特有規(guī)格屬性在spu屬性模板中的對應(yīng)下標(biāo)組合',
  `own_spec` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT 'sku的特有規(guī)格參數(shù)鍵值對，json格式，反序列化時(shí)請使用linkedHashMap，保證有序',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否有效，0無效，1有效',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `key_spu_id`(`spu_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'sku表,該表表示具體的商品實(shí)體' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_goods_spec_group
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_goods_spec_group`;
CREATE TABLE `t_mall_goods_spec_group`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `category_id` bigint(20) NOT NULL COMMENT '商品分類id，一個(gè)分類下有多個(gè)規(guī)格組',
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '規(guī)格組的名稱',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `key_tenant_id`(`tenant_id`) USING BTREE,
  INDEX `key_category_id`(`category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '規(guī)格參數(shù)的分組表，每個(gè)商品分類下有多個(gè)規(guī)格參數(shù)組' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_goods_spec_param
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_goods_spec_param`;
CREATE TABLE `t_mall_goods_spec_param`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `category_id` bigint(20) NOT NULL COMMENT '商品分類id',
  `group_id` bigint(20) NOT NULL COMMENT '所屬組的id',
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '參數(shù)名',
  `numeric` tinyint(1) NOT NULL COMMENT '是否是數(shù)字類型參數(shù)，true或false',
  `unit` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '數(shù)字類型參數(shù)的單位，非數(shù)字類型可以為空',
  `generic` tinyint(1) NOT NULL COMMENT '是否是sku通用屬性，true或false',
  `searching` tinyint(1) NOT NULL COMMENT '是否用于搜索過濾，true或false',
  `segments` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '數(shù)值類型參數(shù)，如果需要搜索，則添加分段間隔值，如CPU頻率間隔：0.5-1.0',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `key_tenant_id`(`tenant_id`) USING BTREE,
  INDEX `key_category_id`(`category_id`) USING BTREE,
  INDEX `key_group_id`(`group_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '規(guī)格參數(shù)組下的參數(shù)名' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_goods_spu
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_goods_spu`;
CREATE TABLE `t_mall_goods_spu`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `brand_id` bigint(20) NOT NULL COMMENT '商品所屬品牌id',
  `category_id` bigint(20) NOT NULL COMMENT '商品分類id',
  `name` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '商品名稱',
  `sub_title` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '副標(biāo)題，一般是促銷信息',
  `on_sale` tinyint(2) NOT NULL DEFAULT 1 COMMENT '是否上架，0下架，1上架',
  `price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '售價(jià)',
  `use_spec` tinyint(2) NOT NULL DEFAULT 1 COMMENT '是否使用規(guī)格：0=不使用，1=使用',
  `spec_groups` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品規(guī)格組',
  `goods_stock` int(11) NOT NULL DEFAULT 0 COMMENT '商品庫存',
  `virtual_sales` int(11) NOT NULL DEFAULT 0 COMMENT '虛擬銷售數(shù)量',
  `confine_count` int(11) NOT NULL DEFAULT -1 COMMENT '購物數(shù)量限制',
  `pieces` int(11) NOT NULL DEFAULT 0 COMMENT '滿件包郵',
  `forehead` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '滿額包郵',
  `freight_id` int(11) NOT NULL COMMENT '運(yùn)費(fèi)模板ID',
  `give_integral` int(11) NOT NULL DEFAULT 0 COMMENT '贈送積分',
  `give_integral_type` tinyint(2) NOT NULL DEFAULT 1 COMMENT '贈送積分類型1固定值 2百分比',
  `deductible_integral` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '可抵扣積分',
  `deductible_integral_type` tinyint(2) NOT NULL DEFAULT 1 COMMENT '可抵扣積分類型1固定值 2百分比',
  `accumulative` tinyint(2) NOT NULL DEFAULT 0 COMMENT '允許多件累計(jì)折扣 0否 1是',
  `individual_share` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否單獨(dú)分銷設(shè)置：0否 1是',
  `share_setting_type` tinyint(2) NOT NULL DEFAULT 0 COMMENT '分銷設(shè)置類型 0普通設(shè)置 1詳細(xì)設(shè)置',
  `share_commission_type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '傭金配比 0 固定金額 1 百分比',
  `membership_price` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否享受會員價(jià)購買',
  `membership_price_single` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否單獨(dú)設(shè)置會員價(jià)',
  `share_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '自定義分享圖片',
  `share_title` varchar(65) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '自定義分享標(biāo)題',
  `is_default_services` tinyint(2) NOT NULL DEFAULT 1 COMMENT '默認(rèn)服務(wù) 0否  1是',
  `sort` int(11) NOT NULL DEFAULT 100 COMMENT '排序',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `key_tenant_id`(`tenant_id`) USING BTREE,
  INDEX `key_category_id`(`category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'spu表，該表描述的是一個(gè)抽象性的商品' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_goods_spu_detail
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_goods_spu_detail`;
CREATE TABLE `t_mall_goods_spu_detail`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `spu_id` bigint(20) NOT NULL,
  `description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述信息',
  `generic_spec` varchar(2048) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '通用規(guī)格參數(shù)數(shù)據(jù)',
  `special_spec` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '特有規(guī)格參數(shù)及可選值信息，json格式',
  `packing_list` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '包裝清單',
  `after_service` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '售后服務(wù)',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `key_tenant_id`(`tenant_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for undo_log
-- ----------------------------
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log`  (
  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',
  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',
  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',
  `rollback_info` longblob NOT NULL COMMENT 'rollback info',
  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',
  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',
  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',
  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
2、訂單數(shù)據(jù)庫表設(shè)計(jì)
-- ----------------------------
-- Table structure for t_mall_order
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_order`;
CREATE TABLE `t_mall_order`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `user_id` bigint(20) NOT NULL COMMENT '主鍵',
  `store_id` int(11) NOT NULL DEFAULT 0 COMMENT '店鋪id',
  `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '訂單號',
  `total_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '訂單總金額(含運(yùn)費(fèi))',
  `total_pay_price` decimal(10, 2) NOT NULL COMMENT '實(shí)際支付總費(fèi)用(含運(yùn)費(fèi)）',
  `express_original_price` decimal(10, 2) NOT NULL COMMENT '運(yùn)費(fèi)',
  `express_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '修改后運(yùn)費(fèi)',
  `total_goods_original_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '訂單商品總金額',
  `total_goods_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '優(yōu)惠后訂單商品總金額',
  `store_discount_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '商家改價(jià)優(yōu)惠',
  `member_discount_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '會員優(yōu)惠價(jià)格',
  `coupon_id` int(11) NULL DEFAULT NULL COMMENT '優(yōu)惠券id',
  `coupon_discount_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '優(yōu)惠券優(yōu)惠金額',
  `integral` int(11) NULL DEFAULT NULL COMMENT '使用的積分?jǐn)?shù)量',
  `integral_deduction_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '積分抵扣金額',
  `name` varchar(65) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '收件人姓名',
  `mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '收件人手機(jī)號',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '收件人地址',
  `comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用戶訂單備注',
  `order_form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '自定義表單（JSON）',
  `leaving_message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '留言',
  `store_comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '商家訂單備注',
  `pay_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否支付：0.未支付 1.已支付',
  `pay_type` tinyint(2) NULL DEFAULT 1 COMMENT '支付方式：1.在線支付 2.貨到付款 3.余額支付',
  `pay_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00' COMMENT '支付時(shí)間',
  `deliver_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否發(fā)貨：0.未發(fā)貨 1.已發(fā)貨',
  `deliver_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00' COMMENT '發(fā)貨時(shí)間',
  `express` varchar(65) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '物流公司',
  `express_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '物流訂單號',
  `confirm_receipt` tinyint(2) NULL DEFAULT 0 COMMENT '收貨狀態(tài)：0.未收貨 1.已收貨',
  `confirm_receipt_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00' COMMENT '確認(rèn)收貨時(shí)間',
  `cancel_status` tinyint(2) NULL DEFAULT 0 COMMENT '訂單取消狀態(tài)：0.未取消 1.已取消 2.申請取消',
  `cancel_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00' COMMENT '訂單取消時(shí)間',
  `recycle_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否加入回收站 0.否 1.是',
  `offline` tinyint(2) NULL DEFAULT 0 COMMENT '是否到店自提：0.否 1.是',
  `offline_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '核銷碼',
  `verifier_id` int(11) NULL DEFAULT 0 COMMENT '核銷員ID',
  `verifier_store_id` int(11) NULL DEFAULT 0 COMMENT '自提門店ID',
  `support_pay_types` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '支持的支付方式',
  `evaluation_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否評價(jià) 0.否 1.是',
  `evaluation_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00',
  `after_sales_out` tinyint(2) NULL DEFAULT 0 COMMENT '是否過售后時(shí)間 0.否 1.是',
  `after_sales_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否申請售后 0.否 1.是',
  `status` tinyint(2) NULL DEFAULT 1 COMMENT '訂單狀態(tài) 1.已完成 0.進(jìn)行中',
  `auto_cancel_time` timestamp(0) NULL DEFAULT NULL COMMENT '自動取消時(shí)間',
  `auto_confirm_verifier_time` timestamp(0) NULL DEFAULT NULL COMMENT '自動確認(rèn)收貨時(shí)間',
  `auto_after_sales_time` timestamp(0) NULL DEFAULT NULL COMMENT '自動售后時(shí)間',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE,
  INDEX `INDEX_USER_ID`(`user_id`) USING BTREE,
  INDEX `INDEX_STORE_ID`(`store_id`) USING BTREE,
  INDEX `INDEX_ORDER_NO`(`order_no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_order_sku
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_order_sku`;
CREATE TABLE `t_mall_order_sku`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `order_id` bigint(20) NOT NULL COMMENT '訂單id',
  `goods_sku_id` bigint(20) NULL DEFAULT NULL COMMENT '購買商品id',
  `goods_sku_number` int(11) NULL DEFAULT NULL COMMENT '購買商品數(shù)量',
  `goods_sku_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '商品單價(jià)',
  `total_original_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '商品總價(jià)',
  `total_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '優(yōu)惠后商品總價(jià)',
  `member_discount_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '會員優(yōu)惠金額',
  `store_discount_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '商家改價(jià)優(yōu)惠',
  `goods_sku_info` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '購買商品信息',
  `refund_status` tinyint(1) NULL DEFAULT 0 COMMENT '是否退款',
  `after_sales_status` tinyint(1) NULL DEFAULT 0 COMMENT '售后狀態(tài) 0--未售后 1--售后中 2--售后結(jié)束',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE,
  INDEX `INDEX_ORDER_ID`(`order_id`) USING BTREE,
  INDEX `INDEX_GOODS_SKU_ID`(`goods_sku_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for undo_log
-- ----------------------------
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log`  (
  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',
  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',
  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',
  `rollback_info` longblob NOT NULL COMMENT 'rollback info',
  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',
  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',
  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',
  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
3、支付數(shù)據(jù)庫表設(shè)計(jì)
DROP TABLE IF EXISTS `t_mall_pay_record`;
CREATE TABLE `t_mall_pay_record`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `user_id` bigint(20) NOT NULL COMMENT '用戶id',
  `order_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0',
  `amount` decimal(9, 2) NOT NULL,
  `pay_status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '支付狀態(tài)：0=未支付，1=已支付, 2=已退款',
  `pay_type` tinyint(2) NOT NULL DEFAULT 3 COMMENT '支付方式：1=微信支付，2=貨到付款，3=余額支付，4=支付寶支付,  5=銀行卡支付',
  `title` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `refund` decimal(9, 2) NOT NULL DEFAULT 0.00 COMMENT '已退款金額',
  `comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '備注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for undo_log
-- ----------------------------
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log`  (
  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',
  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',
  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',
  `rollback_info` longblob NOT NULL COMMENT 'rollback info',
  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',
  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',
  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',
  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
4、賬戶數(shù)據(jù)庫表設(shè)計(jì)
-- ----------------------------
-- Table structure for t_mall_user_account
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_user_account`;
CREATE TABLE `t_mall_user_account`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `user_id` bigint(20) NOT NULL COMMENT '用戶id',
  `integral` bigint(20) NOT NULL DEFAULT 0 COMMENT '積分',
  `balance` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '余額',
  `account_status` tinyint(2) NULL DEFAULT 1 COMMENT '賬戶狀態(tài) \'0\'禁用，\'1\' 啟用',
  `comments` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '備注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE,
  INDEX `INDEX_USER_ID`(`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用戶賬戶表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for t_mall_user_balance_record
-- ----------------------------
DROP TABLE IF EXISTS `t_mall_user_balance_record`;
CREATE TABLE `t_mall_user_balance_record`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租戶id',
  `user_id` bigint(20) NOT NULL COMMENT '用戶id',
  `type` tinyint(2) NOT NULL COMMENT '類型：1=收入，2=支出',
  `amount` decimal(10, 2) NOT NULL COMMENT '變動金額',
  `comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '備注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '創(chuàng)建者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',
  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:刪除 0:不刪除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE,
  INDEX `INDEX_USER_ID`(`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for undo_log
-- ----------------------------
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log`  (
  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',
  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',
  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',
  `rollback_info` longblob NOT NULL COMMENT 'rollback info',
  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',
  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',
  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',
  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
5、上面的腳本中，每個(gè)數(shù)據(jù)都需要刷入了Seata分布式事務(wù)回滾需要的表腳本,在下載Seata包的seata-1.4.1\seata-1.4.1\script\client\at\db路徑下
-- ----------------------------
-- Table structure for undo_log
-- ----------------------------
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log`  (
  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',
  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',
  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',
  `rollback_info` longblob NOT NULL COMMENT 'rollback info',
  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',
  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',
  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',
  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

三、測試代碼

??在GitEgg-Cloud工程下，新建gitegg-mall和gitegg-mall-client子工程，client子工程用于fegin調(diào)用
1、訂單服務(wù)

    @DS("mall_order")//每一層都需要使用多數(shù)據(jù)源注解切換所選擇的數(shù)據(jù)庫
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    @GlobalTransactional //重點(diǎn) 第一個(gè)開啟事務(wù)的需要添加seata全局事務(wù)注解
    @Override
    public void order(List<OrderSkuDTO> orderSkuList, Long userId) {

        //獲取商品的詳細(xì)信息
        Result<Object> goodsSkuResult = mallGoodsFeign.queryByIds(orderSkuList.stream()
                .map(OrderSkuDTO::getGoodsSkuId)
                .collect(Collectors.toList()));
        List<Object> resultSkuList = (List<Object>) goodsSkuResult.getData();
        List<GoodsSkuDTO> goodsSkuList = new ArrayList<>();
        if(CollectionUtils.isEmpty(resultSkuList) || resultSkuList.size() != orderSkuList.size()) {
            throw new BusinessException("商品不存在");
        }
        else {
            resultSkuList.stream().forEach(goodsSku -> {
                GoodsSkuDTO goodsSkuDTO = BeanUtil.fillBeanWithMap((Map<?, ?>) goodsSku, new GoodsSkuDTO(), false);
                goodsSkuList.add(goodsSkuDTO);
            });
        }

        //扣商品庫存
        List<ReduceStockDTO> reduceStockDtoList = orderSkuList.stream()
                .map(t -> new ReduceStockDTO(t.getGoodsSkuId(),t.getGoodsSkuNumber()))
                .collect(Collectors.toList());
        mallGoodsFeign.reduceStock(reduceStockDtoList);

//        //支付
        BigDecimal totalMoney = new BigDecimal(0.0d);
        for(OrderSkuDTO orderSkuDTO: orderSkuList) {
            for(GoodsSkuDTO goodsSkuDTO: goodsSkuList) {
                if(orderSkuDTO.getGoodsSkuId().equals(goodsSkuDTO.getId())) {
                    BigDecimal skuNumber = new BigDecimal(orderSkuDTO.getGoodsSkuNumber());
                    totalMoney = totalMoney.add(goodsSkuDTO.getPrice().multiply(skuNumber));
                    break;
                }
            }
        }

        mallPayFeign.pay(userId, totalMoney);

        //主訂單表插入數(shù)據(jù)
        Order order = new Order();
        order.setTotalPrice(totalMoney);
        order.setTotalPayPrice(totalMoney);
        order.setExpressOriginalPrice(totalMoney);
        order.setStatus(1);
        order.setUserId(userId);
        this.save(order);

        //子訂單表插入數(shù)據(jù)
        ArrayList<OrderSku> orderSkus = new ArrayList<>();
        orderSkuList.forEach(payOrderReq -> {
            OrderSku orderSku = new OrderSku();
            orderSku.setOrderId(order.getId());
            orderSku.setGoodsSkuNumber(payOrderReq.getGoodsSkuNumber());
            orderSku.setGoodsSkuId(payOrderReq.getGoodsSkuId());
            for(GoodsSkuDTO goodsSkuDTO : goodsSkuList) {
                if(payOrderReq.getGoodsSkuId().equals(goodsSkuDTO.getId())) {
                    orderSku.setGoodsSkuPrice(goodsSkuDTO.getPrice());
                    break;
                }
            }
            orderSkus.add(orderSku);
        });
        orderSkuService.saveBatch(orderSkus);
    }

2、商品服務(wù)

    @DS("mall_goods")
    @Override
    public List<GoodsSku> queryGoodsByIds(List<Long> idList) {
        return goodsSkuMapper.queryGoodsByIds(idList);
    }

    /**
     * 事務(wù)傳播特性設(shè)置為 REQUIRES_NEW 開啟新的事務(wù)  重要?。。。∫欢ㄒ褂肦EQUIRES_NEW
     */
    @DS("mall_goods")
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    @Override
    public void reduceStock(List<ReduceStockDTO> reduceStockReqList) {
        reduceStockReqList.forEach(sku -> {
            Integer line = goodsSkuMapper.reduceStock(sku.getNumber(), sku.getSkuId());
            if(line == null || line == 0) {
                throw new BusinessException("商品不存在或庫存不足");
            }
        });
    }

3、支付服務(wù)

    /**
     * 事務(wù)傳播特性設(shè)置為 REQUIRES_NEW 開啟新的事務(wù)    重要?。。。∫欢ㄒ褂肦EQUIRES_NEW
     */
    @DS("mall_pay")
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    @Override
    public Long pay(Long userId, BigDecimal payMoney) {

        //調(diào)用gitegg-mall-user的賬戶扣除余額接口
        mallUserFeign.accountDeduction(userId, payMoney);

        // 插入支付記錄表
        PayRecord payRecord = new PayRecord();
        payRecord.setUserId(userId);
        payRecord.setAmount(payMoney);
        payRecord.setPayStatus(GitEggConstant.Number.ONE);
        payRecord.setPayType(GitEggConstant.Number.FIVE);
        payRecordService.save(payRecord);
        return payRecord.getId();
    }

4、賬戶服務(wù)

    /**
     * 事務(wù)傳播特性設(shè)置為 REQUIRES_NEW 開啟新的事務(wù)    重要?。。。∫欢ㄒ褂肦EQUIRES_NEW
     */
    @DS("mall_user")
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    @Override
    public void deduction(Long userId, BigDecimal amountOfMoney) {
        //查看賬戶余額是否滿足扣款
        QueryUserAccountDTO queryUserAccountDTO = new QueryUserAccountDTO();
        queryUserAccountDTO.setUserId(userId);
        UserAccountDTO userAccount = this.queryUserAccount(queryUserAccountDTO);

        if(userAccount == null) {
            throw new BusinessException("用戶未開通個(gè)人賬戶");
        }

        if(amountOfMoney.compareTo(userAccount.getBalance()) > GitEggConstant.Number.ZERO) {
            throw new BusinessException("賬戶余額不足");
        }
        //執(zhí)行扣款
        userAccountMapper.deductionById(userAccount.getId(), amountOfMoney);

        //加入賬戶變動記錄
        UserBalanceRecord userBalanceRecord = new UserBalanceRecord();
        userBalanceRecord.setUserId(userId);
        userBalanceRecord.setAmount(amountOfMoney);
        userBalanceRecord.setType(GitEggConstant.Number.TWO);
        userBalanceRecordService.save(userBalanceRecord);
    }

5、使用Postman測試，發(fā)送請求，然后查看數(shù)據(jù)庫是否都增加了數(shù)據(jù)，正常情況下，幾個(gè)數(shù)據(jù)庫的表都有新增或更新


請求頭

請求參數(shù)

6、測試異常情況，在代碼中拋出異常，然后進(jìn)行debug，查看在異常之前數(shù)據(jù)庫數(shù)據(jù)是否入庫，異常之后，入庫數(shù)據(jù)是否已回滾。同時(shí)可觀察undo_log表的數(shù)據(jù)情況。

# 在訂單服務(wù)中添加
throw new BusinessException("測試異?；貪L");

四、整合數(shù)據(jù)庫分庫分表

??首先在我們整合dynamic-datasource和shardingsphere-JDBC之前，需要了解它們的異同點(diǎn):dynamic-datasource從字面意思可以看出，它是動態(tài)多數(shù)據(jù)源，其主要功能是支持多數(shù)據(jù)源及數(shù)據(jù)源動態(tài)切換不支持?jǐn)?shù)據(jù)分片，shardingsphere-jdbc主要功能是數(shù)據(jù)分片、讀寫分離，當(dāng)然也支持多數(shù)據(jù)源，但是到目前為止如果要支持多數(shù)據(jù)源動態(tài)切換的話，需要自己實(shí)現(xiàn)，所以，這里結(jié)合兩者的優(yōu)勢，使用dynamic-datasource的動態(tài)多數(shù)據(jù)源切換+shardingsphere-jdbc的數(shù)據(jù)分片、讀寫分離。
1、在gitegg-platform-bom和gitegg-platform-db中引入shardingsphere-jdbc的依賴,重新install。（注意這里使用了5.0.0-alpha版本，正式環(huán)境請使用最新發(fā)布版。）


        <!-- shardingsphere-jdbc -->
        <sharding.jdbc.version>5.0.0-alpha</sharding.jdbc.version>


            <!-- Shardingsphere-jdbc -->
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
                <version>${shardingsphere.jdbc.version}</version>
            </dependency>
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>shardingsphere-jdbc-core-spring-namespace</artifactId>
                <version>${shardingsphere.jdbc.version}</version>
            </dependency>

2、在gitegg-platform-db中，新建DynamicDataSourceProviderConfig類，自定義DynamicDataSourceProvider完成與shardingsphere的集成

/**
 * @author GitEgg
 * @date 2021-04-23 19:06:51
 **/
@Configuration
@AutoConfigureBefore(DynamicDataSourceAutoConfiguration.class)
public class DynamicDataSourceProviderConfig {

    @Resource
    private DynamicDataSourceProperties properties;

    /**
     * shardingSphereDataSource
     */
    @Lazy
    @Resource(name = "shardingSphereDataSource")
    private DataSource shardingSphereDataSource;

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                dataSourceMap.put("sharding", shardingSphereDataSource);
                return dataSourceMap;
            }
        };
    }

    /**
     * 將動態(tài)數(shù)據(jù)源設(shè)置為首選的
     * 當(dāng)spring存在多個(gè)數(shù)據(jù)源時(shí), 自動注入的是首選的對象
     * 設(shè)置為主要的數(shù)據(jù)源之后，就可以支持shardingsphere-jdbc原生的配置方式了
     */
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }
}

3、新建用來分庫的數(shù)據(jù)庫表gitegg_cloud_mall_order0和gitegg_cloud_mall_order1，復(fù)制gitegg_cloud_mall_order中的表結(jié)構(gòu)。
4、在Nacos中分別配置shardingsphere-jdbc和多數(shù)據(jù)源

  # shardingsphere 配置
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      common:
        type: com.alibaba.druid.pool.DruidDataSource
        validationQuery: SELECT 1 FROM DUAL        
      names: shardingorder0,shardingorder1
      shardingorder0:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_order0?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&all owMultiQueries=true&serverTimezone=Asia/Shanghai
        username: root
        password: root
      shardingorder1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_order1?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&all owMultiQueries=true&serverTimezone=Asia/Shanghai
        username: root
        password: root
    rules:
      sharding:
        tables:
          t_mall_order:
            actual-data-nodes: shardingorder$->{0..1}.t_mall_order$->{0..1}
            # 配置分庫策略
            databaseStrategy:
              standard:
                shardingColumn: id
                shardingAlgorithmName: database-inline
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: table-inline-order
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
          t_mall_order_sku:
            actual-data-nodes: shardingorder$->{0..1}.t_mall_order_sku$->{0..1}
            # 配置分庫策略
            databaseStrategy:
              standard:
                shardingColumn: id
                shardingAlgorithmName: database-inline
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: table-inline-order-sku
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: shardingorder$->{id % 2}
          table-inline-order:
            type: INLINE
            props:
              algorithm-expression: t_mall_order$->{id % 2}
          table-inline-order-sku:
            type: INLINE
            props:
              algorithm-expression: t_mall_order_sku$->{id % 2}
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123

5、修改OrderServiceImpl.java的下單方法order注解，數(shù)據(jù)源選擇sharding

    @DS("sharding")//每一層都需要使用多數(shù)據(jù)源注解切換所選擇的數(shù)據(jù)庫
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    @GlobalTransactional //重點(diǎn) 第一個(gè)開啟事務(wù)的需要添加seata全局事務(wù)注解
    @Override
    public void order(List<OrderSkuDTO> orderSkuList, Long userId) {
       ......
    }

6、postman模擬測試調(diào)用下單接口，觀察數(shù)據(jù)庫gitegg_cloud_mall_order0和gitegg_cloud_mall_order1里面的order表數(shù)據(jù)變化，我們發(fā)現(xiàn)，數(shù)據(jù)記錄根據(jù)id取余存放到對應(yīng)的庫和表。這里的配置是使用order表的id，在實(shí)際生產(chǎn)環(huán)境中，需要根據(jù)實(shí)際情況來選擇合適的分庫分表策略。


訂單數(shù)據(jù)根據(jù)分庫分表策略存儲

訂單數(shù)據(jù)根據(jù)分庫分表策略存儲

7、測試引入shardingsphere-jdbc后分布式事務(wù)是否正常，在OrderServiceImpl.java的下單方法order中的最后主動拋出異常，saveBatch之后打斷點(diǎn)，使用postman模擬測試調(diào)用下單接口，到達(dá)斷點(diǎn)時(shí)，查看數(shù)據(jù)是否入庫，放開斷點(diǎn)，拋出異常，然后再查看數(shù)據(jù)是否被回滾。

        orderSkuService.saveBatch(orderSkus);
        throw new BusinessException("測試異常");
斷點(diǎn)

gitegg-mall-pay最新數(shù)據(jù)記錄已入庫

拋出異常后gitegg-mall-pay入庫數(shù)據(jù)被回滾

備注：
1、sharding-jdbc啟動時(shí)報(bào)錯(cuò)java.sql.SQLFeatureNotSupportedException: isValid
解決： 這個(gè)是4.x版本的問題，官方會在5.x結(jié)局這個(gè)問題，目前解決方案是關(guān)閉sql健康檢查。

本文源碼在https://gitee.com/wmz1930/GitEgg 的chapter-27（未使用shardingsphere-jdbc分庫分表）和chapter-27-shardingsphere-jdbc（使用shardingsphere-jdbc分庫分表）分支。

GitEgg-Cloud是一款基于SpringCloud整合搭建的企業(yè)級微服務(wù)應(yīng)用開發(fā)框架，開源項(xiàng)目地址:

Gitee: https://gitee.com/wmz1930/GitEgg
GitHub: https://github.com/wmz1930/GitEgg

歡迎感興趣的小伙伴Star支持一下。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容