当前位置： 首页 > news >正文

导入Seata-server所需SQL

news 2026/7/1 2:41:26

前面我们已经配置了Seata使用mysql作为db高可用数据库(store.mode=db)，故需要在mysql创建一个seata库(store.db.url中配置的库名)，并导入数据库脚本。可以从GitHub仓库https://github.com/apache/incubator-seata/blob/develop/script/server/db下载不同数据库的SQL脚本（直达链接：mysql数据库脚本），下载后将SQL导入数据库中。

-- -------------------------------- The script used when storeMode is 'db' -------------------------------- -- the table to store GlobalSession data CREATE TABLE IF NOT EXISTS `global_table` ( `xid` VARCHAR(128) NOT NULL, `transaction_id` BIGINT, `status` TINYINT NOT NULL, `application_id` VARCHAR(32), `transaction_service_group` VARCHAR(32), `transaction_name` VARCHAR(128), `timeout` INT, `begin_time` BIGINT, `application_data` VARCHAR(2000), `gmt_create` DATETIME, `gmt_modified` DATETIME, PRIMARY KEY (`xid`), KEY `idx_status_gmt_modified` (`status` , `gmt_modified`), KEY `idx_transaction_id` (`transaction_id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4; -- the table to store BranchSession data CREATE TABLE IF NOT EXISTS `branch_table` ( `branch_id` BIGINT NOT NULL, `xid` VARCHAR(128) NOT NULL, `transaction_id` BIGINT, `resource_group_id` VARCHAR(32), `resource_id` VARCHAR(256), `branch_type` VARCHAR(8), `status` TINYINT, `client_id` VARCHAR(64), `application_data` VARCHAR(2000), `gmt_create` DATETIME(6), `gmt_modified` DATETIME(6), PRIMARY KEY (`branch_id`), KEY `idx_xid` (`xid`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4; -- the table to store lock data CREATE TABLE IF NOT EXISTS `lock_table` ( `row_key` VARCHAR(128) NOT NULL, `xid` VARCHAR(128), `transaction_id` BIGINT, `branch_id` BIGINT NOT NULL, `resource_id` VARCHAR(256), `table_name` VARCHAR(32), `pk` VARCHAR(36), `status` TINYINT NOT NULL DEFAULT '0' COMMENT '0:locked ,1:rollbacking', `gmt_create` DATETIME, `gmt_modified` DATETIME, PRIMARY KEY (`row_key`), KEY `idx_status` (`status`), KEY `idx_branch_id` (`branch_id`), KEY `idx_xid` (`xid`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4; CREATE TABLE IF NOT EXISTS `distributed_lock` ( `lock_key` CHAR(20) NOT NULL, `lock_value` VARCHAR(20) NOT NULL, `expire` BIGINT, primary key (`lock_key`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4; INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('AsyncCommitting', ' ', 0); INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('RetryCommitting', ' ', 0); INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('RetryRollbacking', ' ', 0); INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('TxTimeoutCheck', ' ', 0);

至此，Seata-server已经配置完成并且集成了Nacos,事务数据也存储在DB中。可以点击Seata目录下bin/seata-server.bat脚本启动Seata-server。

搭建测试微服务

创建测试库及表

# 订单数据库信息 seata_order DROP DATABASE IF EXISTS seata_order; CREATE DATABASE seata_order; DROP TABLE IF EXISTS seata_order.p_order; CREATE TABLE seata_order.p_order ( id INT(11) NOT NULL AUTO_INCREMENT, user_id INT(11) DEFAULT NULL, product_id INT(11) DEFAULT NULL, amount INT(11) DEFAULT NULL, total_price DOUBLE DEFAULT NULL, status VARCHAR(100) DEFAULT NULL, add_time DATETIME DEFAULT CURRENT_TIMESTAMP, last_update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4; DROP TABLE IF EXISTS seata_order.undo_log; CREATE TABLE seata_order.undo_log ( id BIGINT(20) NOT NULL AUTO_INCREMENT, branch_id BIGINT(20) NOT NULL, xid VARCHAR(100) NOT NULL, context VARCHAR(128) NOT NULL, rollback_info LONGBLOB NOT NULL, log_status INT(11) NOT NULL, log_created DATETIME NOT NULL, log_modified DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY ux_undo_log (xid, branch_id) ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4; # 产品数据库信息 seata_product DROP DATABASE IF EXISTS seata_product; CREATE DATABASE seata_product; DROP TABLE IF EXISTS seata_product.product; CREATE TABLE seata_product.product ( id INT(11) NOT NULL AUTO_INCREMENT, price DOUBLE DEFAULT NULL, stock INT(11) DEFAULT NULL, last_update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4; DROP TABLE IF EXISTS seata_product.undo_log; CREATE TABLE seata_product.undo_log ( id BIGINT(20) NOT NULL AUTO_INCREMENT, branch_id BIGINT(20) NOT NULL, xid VARCHAR(100) NOT NULL, context VARCHAR(128) NOT NULL, rollback_info LONGBLOB NOT NULL, log_status INT(11) NOT NULL, log_created DATETIME NOT NULL, log_modified DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY ux_undo_log (xid, branch_id) ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4; INSERT INTO seata_product.product (id, price, stock) VALUES (1, 10, 20); # 账户数据库信息 seata_account DROP DATABASE IF EXISTS seata_account; CREATE DATABASE seata_account; DROP TABLE IF EXISTS seata_account.account; CREATE TABLE seata_account.account ( id INT(11) NOT NULL AUTO_INCREMENT, balance DOUBLE DEFAULT NULL, last_update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4; DROP TABLE IF EXISTS seata_account.undo_log; CREATE TABLE seata_account.undo_log ( id BIGINT(20) NOT NULL AUTO_INCREMENT, branch_id BIGINT(20) NOT NULL, xid VARCHAR(100) NOT NULL, context VARCHAR(128) NOT NULL, rollback_info LONGBLOB NOT NULL, log_status INT(11) NOT NULL, log_created DATETIME NOT NULL, log_modified DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY ux_undo_log (xid, branch_id) ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4; INSERT INTO seata_account.account (id, balance) VALUES (1, 50);

其中，每个库中的undo_log表，是Seata AT模式必须创建的表，主要用于分支事务的回滚，undo_log表可以从GitHub仓库https://github.com/apache/incubator-seata/tree/develop/script/client/at/db下载不同数据库的SQL脚本（直达链接：mysql数据库脚本）。另外，考虑到测试方便，我们插入了一条id = 1的account记录，和一条id = 1的product记录。

搭建测试服务

搭建账户服务

在ruoyi-modules新建一个Module：ruoyi-account，为了搭建方便，以及减少Maven依赖，pom依赖直接拷贝ruoyi-system中的依赖，然后新增Seata的依赖就可以了。
ruoyi-account的pom.xml:

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>com.ruoyi</groupId> <artifactId>ruoyi-modules</artifactId> <version>3.6.6</version> </parent> <artifactId>ruoyi-account</artifactId> <packaging>jar</packaging> <name>ruoyi-account</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <dependencies> <!-- SpringCloud Alibaba Nacos --> <dependency> <groupId>com.alibaba.cloud</groupId> <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId> </dependency> <!-- SpringCloud Alibaba Nacos Config --> <dependency> <groupId>com.alibaba.cloud</groupId> <artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId> </dependency> <!-- SpringCloud Alibaba Sentinel --> <dependency> <groupId>com.alibaba.cloud</groupId> <artifactId>spring-cloud-starter-alibaba-sentinel</artifactId> </dependency> <!-- SpringBoot Actuator --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> <!-- Mysql Connector --> <dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> </dependency> <!-- RuoYi Common DataSource --> <dependency> <groupId>com.ruoyi</groupId> <artifactId>ruoyi-common-datasource</artifactId> </dependency> <!-- RuoYi Common DataScope --> <dependency> <groupId>com.ruoyi</groupId> <artifactId>ruoyi-common-datascope</artifactId> </dependency> <!-- RuoYi Common Log --> <dependency> <groupId>com.ruoyi</groupId> <artifactId>ruoyi-common-log</artifactId> </dependency> <!-- RuoYi Common Swagger --> <dependency> <groupId>com.ruoyi</groupId> <artifactId>ruoyi-common-swagger</artifactId> </dependency> <!-- SpringBoot Seata --> <dependency> <groupId>com.alibaba.cloud</groupId> <artifactId>spring-cloud-starter-alibaba-seata</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> </dependencies> <build> <finalName>${project.artifactId}</finalName> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <executions> <execution> <goals> <goal>repackage</goal> </goals> </execution> </executions> </plugin> </plugins> </build> </project>

ruoyi-account的bootstrap.yml:

# Tomcat server: port: 10300 # Spring spring: application: # 应用名称 name: ruoyi-account profiles: # 环境配置 active: dev cloud: nacos: discovery: # 服务注册地址 server-addr: 127.0.0.1:8848 config: # 配置中心地址 server-addr: 127.0.0.1:8848 # 配置文件格式 file-extension: yml # 共享配置 shared-configs: - application-${spring.profiles.active}.${spring.cloud.nacos.config.file-extension} seata: enabled: true # Seata 应用编号，默认为 ${spring.application.name} application-id: ${spring.application.name} # Seata 事务组编号，用于 TC 集群名 tx-service-group: ${spring.application.name}-group # 关闭自动代理 enable-auto-data-source-proxy: false # 服务配置项 service: # 虚拟组和分组的映射 vgroup-mapping: ruoyi-account-group: default config: type: nacos nacos: serverAddr: 127.0.0.1:8848 group: SEATA_GROUP namespace: dataId: seataServer.properties registry: type: nacos nacos: application: seata-server server-addr: 127.0.0.1:8848 namespace:

ruoyi-account的bootstrap.yml也是直接拷贝ruoyi-system中的bootstrap.yml，然后修改服务端口，服务名；为了演示方便，关于Seata的配置也直接写在了bootstrap.yml。
ruoyi-account的ruoyi-account-dev.yml:

# spring配置 spring: redis: host: localhost port: 6379 password: datasource: druid: stat-view-servlet: enabled: true loginUsername: ruoyi loginPassword: 123456 dynamic: druid: initial-size: 5 min-idle: 5 maxActive: 20 maxWait: 60000 connectTimeout: 30000 socketTimeout: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 filters: stat,slf4j connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 datasource: # 主库数据源 master: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/seata_account?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8 username: root password: root123 # 从库数据源 # slave: # username: # password: # url: # driver-class-name: seata: true #开启seata代理，开启后默认每个数据源都代理，如果某个不需要代理可单独关闭 # mybatis配置 mybatis: # 搜索指定包别名 typeAliasesPackage: com.ruoyi.account # 配置mapper的扫描，找到所有的mapper.xml映射文件 mapperLocations: classpath:mapper/**/*.xml # springdoc配置 springdoc: gatewayUrl: http://localhost:8080/${spring.application.name} api-docs: # 是否开启接口文档 enabled: false
http://www.jsqmd.com/news/1099724/

相关文章：

  • 四川大学《微积分I-1》期末试卷及答案2016-2025学年PDF
  • OpenHarness源码研究-5-基础设施
  • 什么是配置中心?有哪些常见的配置中心?
  • 【车载 AOSP 16 蓝牙(bluedroid)服务】【qcom 平台双蓝牙】【13.耳机如何协商采样率：从 AVDTP 到 AAC 44100 的一条路】
  • 第六周学习报告
  • 我做了一个基于心理测评和场景记忆的 AI 伴侣产品 CandyAI
  • 爆品之后：新消费品牌如何用数字化穿越增长瓶颈？
  • YOLO目标检测论文实战指南：从模型改进到实验写作全流程
  • 2.1.8 this指针
  • 免费开源NoFences桌面分区管理工具：3步打造高效整洁Windows桌面
  • Day10 | SFT 训练实操——用 QLoRA 微调 Qwen3-8B
  • BetterJoy完整指南：让Switch手柄在PC游戏上完美运行
  • 智谱大模型LLM一面，人麻了！！！
  • 【JAVA毕设源码分享】基于springboot的小区公共收益管理系统 的设计与实现(程序+文档+代码讲解+一条龙定制)
  • 光电经纬仪测量中的坐标系体系及其应用
  • CPT Markets：把外汇用户支持体系做到位——维度复盘与提示整理
  • 抖音内容批量采集与智能管理工具：从零到精通的完整指南
  • OpenAI / Claude API 报错 401、403、429 怎么解决？一文讲清 API Key 失效排查思路
  • 量子虚时演化算法原理与sine-Gordon模型模拟实践
  • FreeCAD源码分析: Property View
  • 我一个人 11 天交付了两个模块——不是会分身，是让两个 AI 打了配合
  • 1115.交替打印FooBar
  • 【课程设计/毕业设计】基于 SpringBoot 的农业设备销售订单管理系统的设计与实现 基于 SpringBoot 的智慧农机综合服务管理系统【附源码、数据库、万字文档】
  • 修改很简单，但网上讲这点的文档不多，因此多记一笔。另外基于out_ptr会临时转移所有权这点来看，共享所有权模型的std::shared_ptr其实并不适合使用out_ptr，虽然标准没有禁止甚至还要
  • playwright-拖拽验证码
  • LeWorldModel：基于JEPA的轻量化世界模型实践指南
  • 为什么要将 RTF 转换为 PDF？
  • 告别泰拉瑞亚原版限制：tModLoader模组开发实战手册
  • Opencv延迟优化
  • 项目包含项目源码、项目文档、数据库脚本、软件工具等资料；